Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When a user is granted rights to something in SQL Server using the GRANT function where is it recorded?

share|improve this question
    
Which version of MSSQL? –  Satanicpuppy Mar 25 '10 at 15:30
    
I actually don't have a particular version of SQL in mind for this but just a general inquiry. –  Sean Howat Mar 25 '10 at 15:53
add comment

3 Answers

up vote 1 down vote accepted

Try these:

In the "Master" database:

sys.syslogins
sys.sysusers
sys.sql_logins

I think the INFORMATION_SCHEMA.TABLE_PRIVILEGES is only a list of what is allowed to be granted, and by whom.

There is a sys.syspermissions table, but it's wildly ugly to look at, and I'm not wholly sure what it's storing.

Edit: It's none of the above. Instead it's the aptly named sys.database_permissions table. Now I feel stupid.

share|improve this answer
    
sys.database_permissions looks like it's the one. Thanks. And holy smokes that sys.syspermissions is quite ugly! –  Sean Howat Mar 25 '10 at 15:51
add comment

Server level permissions are stored in internal tables exposed through the sys.server_premissions catalog view.

Database level permissions are stored in internal tables exposed through the sys.database_permissions catalog view.

A session can check its permissions by interrogating the built-in fn_my_permissions table function.

To verify if a sessions has a specific permission, use the HAS_PERMS_BY_NAME function.

share|improve this answer
    
Good stuff. That HAS_PERMS_BY_NAME function should help me track down some other permission information I'm curious about. Thanks. –  Sean Howat Mar 26 '10 at 13:43
add comment

I'd look first in the master database. Try INFORMATION_SCHEMA.TABLE_PRIVILEGES.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.