I know that I can query effective permissions by using sys.fn_my_permissions
:
USE myDatabase;
SELECT * FROM fn_my_permissions('dbo.myTable', 'OBJECT')
entity_name | subentity_name | permission_name
------------------------------------------------
dbo.myTable | | SELECT
dbo.myTable | | UPDATE
...
This tells me whether the current user has SELECT, INSERT, UPDATE, etc. permissions on myTable
in database myDatabase
.
Is it possible to easily find out why the user has these permissions? For example, I'd love to have a function fn_my_permissions_ex
which outputs an additional reason
column:
USE myDatabase;
SELECT * FROM fn_my_permissions_ex('dbo.myTable', 'OBJECT')
entity_name | subentity_name | permission_name | reason
------------------------------------------------------------------------------------------------------------------------------------
dbo.myTable | | SELECT | granted to database role public
dbo.myTable | | UPDATE | member of group MYDOMAIN\Superusers, which belongs to database role db_datawriter
...
Unfortunately, I could not find such a function in the SQL Server documentation. Is there a tool or script that provides this functionality?
Best Answer
You can find some good information regarding security from the article below.
Reviewing SQL Server Permissions | TechRepublic http://tek.io/KfzEyp
Except: