When a user is granted rights to something in SQL Server using the GRANT function where is it recorded?
Which version of MSSQL?– SatanicpuppyMar 25, 2010 at 15:30
I actually don't have a particular version of SQL in mind for this but just a general inquiry.– ShaneMar 25, 2010 at 15:53
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.
sys.database_permissions looks like it's the one. Thanks. And holy smokes that sys.syspermissions is quite ugly!– ShaneMar 25, 2010 at 15:51
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.
Good stuff. That HAS_PERMS_BY_NAME function should help me track down some other permission information I'm curious about. Thanks.– ShaneMar 26, 2010 at 13:43
I'd look first in the master database. Try INFORMATION_SCHEMA.TABLE_PRIVILEGES.