Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 1 down vote accepted

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.

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

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

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

share|improve this answer

Your Answer


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.