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.
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.
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.
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With