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

  • Which version of MSSQL? Mar 25, 2010 at 15:30
  • I actually don't have a particular version of SQL in mind for this but just a general inquiry.
    – Shane
    Mar 25, 2010 at 15:53

3 Answers 3


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.

  • sys.database_permissions looks like it's the one. Thanks. And holy smokes that sys.syspermissions is quite ugly!
    – Shane
    Mar 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.
    – Shane
    Mar 26, 2010 at 13:43

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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