User Permissions on a particular table

Question

Post reply

User Permissions on a particular table

Sanz

Hall of Fame

Points: 3412
More actions
January 6, 2010 at 3:27 am

#156552

I am trying to find a way to list all the users/groups that have permissions to a particular table in a database. A query would be ideal.
Thanx in Advance !
Sanz

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 1

Hope this link helps

http://www.tek-tips.com/viewthread.cfm?qid=1488839&page=10

ss-457805 SSCertifiable Points: 5872 More actions · Answer 2

This should work:

select * from sys.database_principals

blog: http://sarveshsingh.com Twitter: @sarveshsing

Sanz Hall of Fame Points: 3412 More actions · Answer 3

Let me put it this way.. I have a table abc and I need to know the groups/users who can access the table abc...

Sanz

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Did you try using sp_helprotect?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sanz Hall of Fame Points: 3412 More actions · Answer 5

Yes.. Tried that but it returns the following message...

Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 291

There are no matching rows on which to report.

Correct me if I am wrong... I guess that is because there are no explicit permissions for any users on the table..

Sanz

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

Sanz (1/6/2010)
Yes.. Tried that but it returns the following message...
Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 291
There are no matching rows on which to report.
Correct me if I am wrong... I guess that is because there are no explicit permissions for any users on the table..

As you wrote, it means that no one has explicit permissions to work on this table. Of course administrators and members of roles such as db_datareaders or db_owners can use this table.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/