9

When I say effective permissions, I'm referring to the permissions listed when you go into the properties of a database in SQL Server Management Studio, click "Permissions", and then click the "Effective" tab.

So far, I have been able to determine the explicit permissions with the following code:

using Microsoft.SqlServer.Management.Smo;
...
DatabasePermissionInfo[] permissions = database.EnumDatabasePermissions("username");

However, I still need to obtain the effective permissions. In this scenario, I added a login for a user and gave it the role of db_datareader and db_datawriter for a database through the User Mapping.

In the permissions for the database, the effective permissions listed are CONNECT, DELETE, INSERT, SELECT, and UPDATE, but the explicit permissions only list connect (which is the only thing that the above code pulls back). So is there a way to programmatically retrieve the effective permissions as well?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
athom
  • 1,257
  • 4
  • 13
  • 30
  • 1
    try this stackoverflow previous posting should help http://stackoverflow.com/questions/962482/sql-permissions-to-add-data-and-how-to-verify – MethodMan Oct 08 '12 at 20:31

2 Answers2

12

I believe you can call sys.fn_my_permissions:

execute as user = 'SomeUserName' -- Set this to the user name you wish to check
select * from fn_my_permissions(null, 'DATABASE') -- Leave these arguments, don't change to MyDatabaseName
order by subentity_name, permission_name
revert

This gave me the same results as the SSMS option you mentioned.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • 1
    His answer is excellent but for the situation where you have migrated your instance and changed your users and logins in the process. Then you can't use the "execute as user" technique, and will have to use other methods. The answer given by user645280 is great in this case. – Hansjp Jul 05 '17 at 12:06
4

Looks like sys.database_principals and sys.database_permissions have some nice information in them about this. This thread: SQL Server query to find all permissions/access for all users in a database talks about it further.

Note: I'm assuming C# calls into the DB are acceptable solutions.

Community
  • 1
  • 1