Where are granted permissions stored in SQL Server?

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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