Ideal Performance - Explicit Permissions, or Group Permissions

Ideal Performance - Explicit Permissions, or Group Permissions

Post by Mike » Sun, 04 Nov 2001 07:39:02



A basic question for those of you more familiar with the guts of SQL Server.  
(I have not had a chance to read Kalen's book yet, shame on me)

Is there any reason to believe that SQL Server performance (sql 7.0) would
increase if:

The Logon used to access the database that is currently a member of the
public group, and and all objects are accessable by the public group, is
given explicit permissions to that database rather than access through the
public group?

I.E. the database role is changed from only "Public" to

Public
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter

A vendor scrambling to resolve a performance issue is suggesting this with
only vauge undocumented testing and I'm just curious if there is any
architectural reason why performance may increase by just giving a user all
roles.

Thanks

 
 
 

Ideal Performance - Explicit Permissions, or Group Permissions

Post by Andrew J. Kell » Mon, 05 Nov 2001 00:21:08


Mike,

I would be very surprised to see any worthwhile increase in performance in
that scenario. The fastest would probably be to have all objects owned by
dbo and the user as dbo so that sql would only have to essentially check
permissions once. The worst might be if all the objects were owned by
different owners and sql would have to check permissions on each and every
object accessed. But the time it takes to do these permission checks should
not be a significant factor in your overall performance.  It sounds more
like just plain bad code and or db design.  Use profiler to see what the
biggest hogs are as far as time / cpu and disk I/O. You can then tune the
real source of the problems.

--
Andrew J. Kelly,   SQL Server MVP


Quote:> A basic question for those of you more familiar with the guts of SQL
Server.
> (I have not had a chance to read Kalen's book yet, shame on me)

> Is there any reason to believe that SQL Server performance (sql 7.0) would
> increase if:

> The Logon used to access the database that is currently a member of the
> public group, and and all objects are accessable by the public group, is
> given explicit permissions to that database rather than access through the
> public group?

> I.E. the database role is changed from only "Public" to

> Public
> db_owner
> db_accessadmin
> db_securityadmin
> db_ddladmin
> db_backupoperator
> db_datareader
> db_datawriter
> db_denydatareader
> db_denydatawriter

> A vendor scrambling to resolve a performance issue is suggesting this with
> only vauge undocumented testing and I'm just curious if there is any
> architectural reason why performance may increase by just giving a user
all
> roles.

> Thanks


 
 
 

Ideal Performance - Explicit Permissions, or Group Permissions

Post by Dan Guzma » Mon, 05 Nov 2001 00:40:55


I agree with Andrew's assessment.  I did some cursory testing and found no
noticeable performance difference.  I'd like to see the vendor provide some
credible rational for their recommendation.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------

 
 
 

Ideal Performance - Explicit Permissions, or Group Permissions

Post by Erland Sommarsko » Tue, 06 Nov 2001 06:53:04



Quote:> I would be very surprised to see any worthwhile increase in performance in
> that scenario. The fastest would probably be to have all objects owned by
> dbo and the user as dbo so that sql would only have to essentially check
> permissions once.

The best is to use stored procedures only, and not issue DML statements
from the client or use dynamic SQL. Because with stored procedures only,
SQL Server only needs to check for permissions on the procedures. Add to
that when you call procedures from the client that if you add the owner,
which always should be dbo, SQL Server will not have to look for a
procedure owned by the current user. In such case it does not matter
whether the current user is dbo or not.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

 
 
 

Ideal Performance - Explicit Permissions, or Group Permissions

Post by Andrew J. Kell » Tue, 06 Nov 2001 09:19:00


Well yes, by objects I was referring to stored procedures that then accessed
the tables.

--
Andrew J. Kelly,   SQL Server MVP



> > I would be very surprised to see any worthwhile increase in performance
in
> > that scenario. The fastest would probably be to have all objects owned
by
> > dbo and the user as dbo so that sql would only have to essentially check
> > permissions once.

> The best is to use stored procedures only, and not issue DML statements
> from the client or use dynamic SQL. Because with stored procedures only,
> SQL Server only needs to check for permissions on the procedures. Add to
> that when you call procedures from the client that if you add the owner,
> which always should be dbo, SQL Server will not have to look for a
> procedure owned by the current user. In such case it does not matter
> whether the current user is dbo or not.

> --
> Erland Sommarskog, Abaris AB

> SQL Server MVP

 
 
 

Ideal Performance - Explicit Permissions, or Group Permissions

Post by Mike » Thu, 08 Nov 2001 00:03:17




Quote:> I agree with Andrew's assessment.  I did some cursory testing and found
> no noticeable performance difference.  I'd like to see the vendor
> provide some credible rational for their recommendation.

> Hope this helps.

...

Thanks for the feedback people.

I don't find any real change in the system after the change the vendor
recommended. This is a software product written in Uniface that targets
multiple databases.  A simple show me customers that have a number that
begins with 2 turns into a call to sp_cursorprepare, sp_cursorexecute, and
then a sp_cursorfetch for every row in the result set.

Not fast at all.  Unfortuantly, the developers of this product don't have a
good understanding of data.  I'm still waiting for the credible rational.  
Last I heard,  they called someone else here to ask them what I was looking
for.

Thanks