如何以编程方式确定我是否对DB具有执行权限?

SQL 2005及更高版本,您可以使用 HAS_PERM_BY_NAME

SELECT HAS_PERMS_BY_NAME('sp_foo', 'OBJECT', 'EXECUTE');

您可以运行如下查询:

SELECT
    o.NAME,COALESCE(p.state_desc,'?permission_command?')+' '+COALESCE(p.permission_name,'?permission_name?')+' ON ['+SCHEMA_NAME(o.schema_id)+'].['+COALESCE(o.Name,'?object_name?')+'] TO ['+COALESCE(dp.Name,'?principal_name?')+']' COLLATE SQL_Latin1_General_CP1_CI_AS AS GrantCommand
    FROM sys.all_objects                          o
        INNER JOIN sys.database_permissions       p ON o.OBJECT_ID=p.major_id
        LEFT OUTER JOIN sys.database_principals  dp ON p.grantee_principal_id = dp.principal_id
    where p.state_desc='GRANT' AND p.permission_name='EXECUTE'
        AND o.NAME='YourProcedureName'
        AND dp.Name='YourSecurityName'

…并删除grant命令的奇特格式,该命令仅供参考

这些也很好。。。

SELECT * FROM fn_my_permissions('YourTable', 'OBJECT') 
SELECT * FROM fn_my_permissions('YourProcedure', 'OBJECT') 
SELECT * FROM fn_my_permissions (NULL, 'DATABASE')
SELECT * FROM fn_my_permissions(NULL, 'SERVER')

要查看其他人拥有哪些权限,可以执行以下操作:

EXECUTE AS user = 'loginToTest'
GO
PRINT 'SELECT permissions on tables:'
SELECT
    HAS_PERMS_BY_NAME(    QUOTENAME(SCHEMA_NAME(schema_id))+'.' + QUOTENAME(name)
                          ,'OBJECT','SELECT'
                     ) AS have_select
        , * 
    FROM sys.tables;

PRINT 'EXECUTE permissions on stored procedures:'
SELECT
    HAS_PERMS_BY_NAME(    QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
        ,'OBJECT', 'EXECUTE') AS have_execute
        , * 
    FROM sys.procedures;
GO
REVERT;
GO

此答案的第一部分显示如何签入权限 T-SQL (请注意,EF版本之间存在差异-给出的示例为EF 4,但可以轻松更改为较新版本):

第一部分(SQL):

T-SQL 用于检查权限的脚本。它首先检查您是否拥有任何权限,然后检查SP的执行权限,最后选择表的权限。看见 this 链接以获取更多信息。

-- 1. Do I have any permissions?
SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') DoIHaveAnyRights;

-- 2. create list of schemas
declare @SchemaList table (schema_id int, name nvarchar(max));

PRINT 'Schemas regarded:'

insert into @SchemaList
select distinct schema_id, name FROM sys.schemas
where name in ('dbo') -- enter the schemas you like to check comma-separated

SELECT s.name, s.schema_id FROM sys.schemas s
join @SchemaList sl on s.schema_id=sl.schema_id

-- 3. check execute permissions
PRINT 'EXECUTE permissions on stored procedures:'
SELECT 
    HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
        ,'OBJECT', 'EXECUTE') AS [have execute]
        ,'['+s.name +'].['+ t.name+']' as [object]
        --, * 
    FROM sys.procedures t
    join @SchemaList s on t.schema_id=s.schema_id
order by s.name, t.name;

-- 4. check select permissions
PRINT 'SELECT permissions on tables:'
SELECT 
    HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(t.schema_id))+'.' + QUOTENAME(t.name)
        ,'OBJECT','SELECT') AS [have select]
        ,'['+s.name +'].['+ t.name+']' as [object]
        --, * 
    FROM sys.tables t
    join @SchemaList s on t.schema_id=s.schema_id
order by s.name, t.name;

和 例如,这将为您提供以下结果:

您可以配置步骤2中考虑的模式。如果您不需要检查有限的模式集,您只需注释掉 where 条款 insert into @SchemaList

第二部分(实体框架):

让我们假设你想检查一下你是否有任何权利 使用LINQ查询中的任何表。看看这个例子(为了简单起见,我在 LinqPad System.Data.Entity.dll 并在运行之前通过F4访问其名称空间):

void Main()
{
    var dc=this;
    var sql="SELECT TOP 1 "
              + "HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY') DoIHaveAnyRights;";
    var result=dc.ExecuteStoreQuery<Rights>(sql);
    if (result1.DoIHaveAnyRights==1)
    {
        Console.WriteLine("OK"); // ok
    }
    else
    {
        // no rights: Show error etc.
        Console.WriteLine("No rights"); // ok
    }
}

public class Rights
{
    public Int32 DoIHaveAnyRights { get; set; }
}

同样,您可以使用我回答的第一部分中的查询,例如:

var sql="select top 1 case when cnt.NoRightsCount=0 then 1 else 0 end "
+"as DoIHaveAnyRights "
+"from (SELECT count(1) NoRightsCount FROM sys.procedures t "
+"where HAS_PERMS_BY_NAME("
+"QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)"
+",'OBJECT', 'EXECUTE')<>1) cnt";

此查询将检查数据库中是否存在您无权执行的存储过程—在本例中,返回值为 result1.DoIHaveAnyRights!=1

我认为您已经了解了这个想法,并尝试了各种可能性:请记住,EF需要访问您要映射到的所有数据库表、存储过程等,您可以在访问它们之前使用上面的代码进行检查。不幸的是,目前没有更简单的方法来做到这一点。

您必须有权访问数据库的DataDictionary,并对其运行查询,以确定您登录的帐户具有哪些权限。这将因数据库而异。