ansaurus

Question

sql server: permissions on table

Answer 1

+1 A:

http://msdn.microsoft.com/en-us/library/ms189121.aspx

CodeToGlory 2010-06-15 21:18:45

Answer 2

A:

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')

Remus Rusanu 2010-06-15 22:33:06

This script did not work for me...

dotnet-practitioner 2010-06-16 00:57:04

I get a blank row for my table..

dotnet-practitioner 2010-06-16 00:57:44

the query works fine. A 'blank row' (an empty result set) is a valid response. Is actually the expected result for a table on which no explicit grants were issued.

Remus Rusanu 2010-06-16 05:01:26

So... if I get a blank row... how do I know ... who has what permissions on this table? Thanks..

dotnet-practitioner 2010-06-16 16:33:07

There are implicit permissions, the ones derived from the fixed roles: db_owner members have all permissions, db_datareader members can select from it, db_datawriter member can update it etc etc. And all other permissions that are derived from ownership chaining. Read the link in the post.

Remus Rusanu 2010-06-16 16:50:56

Try granting an explicit right, like `GRANT SELECT ON <table> TO guest` and then run again the query to see the non-implicit permission grant.

Remus Rusanu 2010-06-16 17:02:09

ansaurus

tags:

views:

answers:

sql server: permissions on table

related questions