Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: Permissions on table

Tags:

sql

sql-server

SQL Server 2008 :

How do I find out what kind of roles have what kind of permissions on a given table.

Thank you in advance.

like image 437
dotnet-practitioner Avatar asked Jun 15 '10 21:06

dotnet-practitioner


People also ask

How can I see the table permissions in SQL Server?

Under Object Explorer, expand the Databases directory and then, expand the required database that contains the table. Next, expand the Tables directory and right-click the required table for which you want to check permissions, and click on the “Properties” option.

How do I get a list of users access to a table in SQL Server?

SQL Server: Find Users in SQL Server Answer: In SQL Server, there is a system view called sys. database_principals. You can run a query against this system view that returns all of the Users that have been created in SQL Server as well as information about these Users.


1 Answers

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

like image 103
Remus Rusanu Avatar answered Nov 06 '22 17:11

Remus Rusanu