如何通过C#确定SQL Server数据库用户的有效权限? [英] How to determine the effective permissions for a user of a SQL Server database through C#?

问题描述

当我说有效权限时，我指的是当您进入 SQL Server Management Studio 中的数据库属性，单击权限"，然后单击有效"选项卡时列出的权限.

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");

但是，我仍然需要获得有效权限.在这种情况下，我为用户添加了一个登录名，并通过用户映射为其赋予了数据库的db_datareader 和db_datawriter 角色.

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.

在数据库的权限中，列出的有效权限为CONNECT、DELETE、INSERT、SELECT、和UPDATE，但显式权限仅列出connect(即上面代码唯一撤回的东西).那么有没有办法以编程方式检索有效权限呢?

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?

谢谢.

推荐答案

相信你可以调用sys.fn_my_permissions:

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

这给了我与您提到的 SSMS 选项相同的结果.

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

这篇关于如何通过C#确定SQL Server数据库用户的有效权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！