如何通过C#确定SQL Server数据库用户的有效权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12788904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine the effective permissions for a user of a SQL Server database through C#?
提问by athom
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.
当我说有效权限时，我指的是在 SQL Server Management Studio 中进入数据库属性时列出的权限，单击“权限”，然后单击“有效”选项卡。
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_datareaderand db_datawriterfor a database through the User Mapping.
但是，我仍然需要获得有效的权限。在这个场景中，我为用户添加了一个登录名，db_datareader并db_datawriter通过用户映射为其赋予了数据库的角色。
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?
在对数据库的权限中，列出的有效权限是CONNECT, DELETE, INSERT, SELECT,and UPDATE，但显式权限只列出 connect （这是上面代码唯一拉回来的）。那么有没有办法以编程方式检索有效权限？
Thanks.
谢谢。
采纳答案by Tim Lehner
I believe you can call sys.fn_my_permissions:
我相信你可以打电话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.
这给了我与您提到的 SSMS 选项相同的结果。
回答by athom
Looks like sys.database_principalsand sys.database_permissionshave some nice information in them about this. This thread: SQL Server query to find all permissions/access for all users in a databasetalks about it further.
看起来像sys.database_principals并且sys.database_permissions在其中有一些关于此的很好的信息。这个线程：SQL Server query to find all permissions/access for all users in a database in a database进一步讨论了它。
Note: I'm assuming C# calls into the DB are acceptable solutions.
注意：我假设对数据库的 C# 调用是可接受的解决方案。
