Sean Howat Asked: 2010-03-26 06:33:12 +0800 CST2010-03-26 06:33:12 +0800 CST 2010-03-26 06:33:12 +0800 CST Where are granted permissions stored in SQL Server? 772 When a user is granted rights to something in SQL Server using the GRANT function where is it recorded? sql-server permissions 3 Answers Voted Remus Rusanu 2010-03-26T15:30:35+08:002010-03-26T15:30:35+08:00 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. Best Answer Satanicpuppy 2010-03-26T07:34:55+08:002010-03-26T07:34:55+08:00 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. wbogacz 2010-03-26T07:29:08+08:002010-03-26T07:29:08+08:00 I'd look first in the master database. Try INFORMATION_SCHEMA.TABLE_PRIVILEGES.
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.
Try these:
In the "Master" database:
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.I'd look first in the master database. Try INFORMATION_SCHEMA.TABLE_PRIVILEGES.