CODEWITHSUNDEEP
sqlsql-server
dotnet-practitioner
dotnet-practitioner

Reputation: 14148

SQL Server: Permissions on table

How do I find out what kind of roles have what kind of permissions on a given table in SQL Server 2008?

Upvotes: 11

Views: 35637

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294177

You can get all permissions granted in the database and filter out for your table:

select permission_name, state, pr.name
from sys.database_permissions pe
join sys.database_principals pr on pe.grantee_principal_id = pr.principal_id
where pe.class = 1 
    and pe.major_id = object_id('<table_name>')
    and pe.minor_id = 0;

In addition you need to add the built in role permissions (db_owner, db_datareader, db_datawriter etc). Objects may also be accessed through ownership chaining.

You can always find out your own effective permission on any object by using fn_my_permissions('table_name', 'OBJECT')

Upvotes: 9

Related Questions