I'm using MS SQL Server 2008, and I am trying to get a complete listing of all privileges assigned to all grantees by running this query:
select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES
However, right now I'm only getting back records associated with the "public" grantee. I've tried adding the db_owner role to this user account, and I've also run this query within the database:
GRANT VIEW DEFINITION TO myuser
Neither of these actions has changed the response from the above query – still only getting public grantee records. What is the minimum setting I need to do to grant the user the rights to all the records?
Thanks
Best Answer
This information_schema view (my bold)
This doesn't take into account roles etc: it all works on
DATABASE_PRINCIPAL_ID
which can be seen if you look at the definition of INFORMATION_SCHEMA.TABLE_PRIVILEGESPersonally, I'd just use sys.database_permissions and not bother with the information_schema rubbish. This relies solely on Meta data visibility to filter rows, so you'll see the actual permissions unfiltered by the INFORMATION_SCHEMA.TABLE_PRIVILEGES view which adds a further filter that bollixes you.
For completeness, here is the view. Note the implicit "old style" JOIN :-)