Permissions on Objects

[Xin Li]

Hi,

I am a relative newbe here, and a newbe to SQL Server in general. So be
gentle I was wondering if there was anyway I could use a stored procedure
(or by any other means) to see if a particular user has a particular permission
on a certain object. The kind of tasks I'd want to perform would be something
like does the user "jane2" have EXECUTE permission on sp_password, or does
"tom" have SELECT permission on CustomerList table.

The primary motivation behind this painful excercise is to implement security
on a website. The site is driven by a DB, and certain people play certain
roles in administrating the site. So for every backend page, the ASP code
has to check to see if the user should be able to access the site. The way
I thought to do this is to have the ASP code some how check to see if the
user has a certain set of permissions on a certain set of stored procedures
and views and possibly tables. This way, I can use SQL server's own security
infrastructure to manage everything without having to design a virtual security
system with my own tables and such.

Normally, this would be a relatively simple task, if I were only checking
on the particular user account. However, I also need to take into consideration
that the user could be a member in different database roles, and those roles
may have been granted certain permission sets on different objects. And
thus, the user's effective permission set would be the union of all permissions
from all roles to which the user belongs (Note: I am using SQL Server 7.0).
And of course if the user is denied a cerain permission at any juncture,
then the user is entirely denied.

I know that SQL Server internally grabs all roles in which a user belongs
from the sysmembers table, and from then on, it does a whole bunch of stuff
with the syspermissions table to see if a user has permissions to a certain
object. But I am not clear exactly what is involved. And I'd rather not
have to reinvent the wheel if I don't have to.

Any tips or pointers you can give would be greatly appreciated. Thank you
very much in advance.

Xin Li

[Andrew Prosser]

"Xin Li" <xinli1@uiuc.edu> wrote:
>
>Hi,
>
>I am a relative newbe here, and a newbe to SQL Server in general. So be
>gentle I was wondering if there was anyway I could use a stored procedure
>(or by any other means) to see if a particular user has a particular permission
>on a certain object. The kind of tasks I'd want to perform would be something
>like does the user "jane2" have EXECUTE permission on sp_password, or does
>"tom" have SELECT permission on CustomerList table.
>
>The primary motivation behind this painful excercise is to implement security
>on a website. The site is driven by a DB, and certain people play certain
>roles in administrating the site. So for every backend page, the ASP code
>has to check to see if the user should be able to access the site. The
way
>I thought to do this is to have the ASP code some how check to see if the
>user has a certain set of permissions on a certain set of stored procedures
>and views and possibly tables. This way, I can use SQL server's own security
>infrastructure to manage everything without having to design a virtual security
>system with my own tables and such.
>
>Normally, this would be a relatively simple task, if I were only checking
>on the particular user account. However, I also need to take into consideration
>that the user could be a member in different database roles, and those roles
>may have been granted certain permission sets on different objects. And
>thus, the user's effective permission set would be the union of all permissions
>from all roles to which the user belongs (Note: I am using SQL Server 7.0).
> And of course if the user is denied a cerain permission at any juncture,
>then the user is entirely denied.
>
>I know that SQL Server internally grabs all roles in which a user belongs
>from the sysmembers table, and from then on, it does a whole bunch of stuff
>with the syspermissions table to see if a user has permissions to a certain
>object. But I am not clear exactly what is involved. And I'd rather not
>have to reinvent the wheel if I don't have to.
>
>Any tips or pointers you can give would be greatly appreciated. Thank you
>very much in advance.
>
>Xin Li
>

You would make your life easier if you just checked the users role, but to
check explicit permissions you can execute sp_helprotect or you can use the
system function 'select permissions(object_id)' for example,

Regards,

Andrew

[Xin Li]

>You would make your life easier if you just checked the users >role, but
to check explicit permissions you can execute >sp_helprotect or you can use
the system function 'select >permissions(object_id)' for example,

Thanks for the tip. Checking role membership would be much easier. I was
wondering if you could point me to a list of all the stored procedures concerned
with security, and what parameters they take, and which return values they
give back. Thanks..

BTW, how would I get the objectID of something in the DB? Thanks again.

Xin Li

[DaveSatz]

In BOL there is a categorized listing of system SP's under "System Stored
Procedures (T-SQL)" including links to SP like sp_helprotect .

"Xin Li" <xinli1@uiuc.edu> wrote in message news:3a50f740$1@news.devx.com...
>
> >You would make your life easier if you just checked the users >role, but
> to check explicit permissions you can execute >sp_helprotect or you can
use
> the system function 'select >permissions(object_id)' for example,
>
> Thanks for the tip. Checking role membership would be much easier. I was
> wondering if you could point me to a list of all the stored procedures
concerned
> with security, and what parameters they take, and which return values they
> give back. Thanks..
>
> BTW, how would I get the objectID of something in the DB? Thanks again.
>
> Xin Li

[Xin Li]

"DaveSatz" <davidsatz@yahoo.com> wrote:
>In BOL there is a categorized listing of system SP's under "System Stored
>Procedures (T-SQL)" including links to SP like sp_helprotect .

Thanks, I found a similar documentation on Microsoft's support site. For
anyone interested, it's at:

http://support.microsoft.com/support...ures_syssp.asp