DMV引起错误的权限选择

Question

我需要赋予特定用户对DM视图的访问权限。

因此，我尝试在SP上授予VIEW DATABASE STATE和VIEW SERVER STATE以及一个EXECUTE，这是在查询sys.dm_exec_query_stats。

它不起作用，我试着在指定的车管所授予SELECT。

我得到了错误'Cannot find the user <UserName>, because it does not exist or you do not have permission.'，下面的脚本中的所有内容都与指定的用户很好地工作，但是最后一条语句，即Grant。有猜到原因吗？

Use MyDatabase
GRANT EXECUTE ON [MySchema].[SP_MySPROC] TO UserName
GRANT VIEW DATABASE STATE TO UserName

USE master;
GRANT VIEW SERVER STATE TO UserName
GRANT SELECT ON sys.dm_exec_query_stats TO UserName

Answer 1

指定的UserName必须是数据库主体(数据库用户)，而不是服务器主体(登录)。我希望显式地将用户添加到主数据库将解决格兰特问题，但您可以考虑使用提供所需权限的证书对proc进行签名。证书方法更复杂，但更安全。下面是一个证书示例。

USE master;

-- create master database access certificate
CREATE CERTIFICATE MasterDatabaseUserCertificate
   ENCRYPTION BY PASSWORD = 'ksfd78#$%@8ks1@b@'
   WITH SUBJECT = 'Master database access certificate',
   START_DATE = '20020101', EXPIRY_DATE = '20990101';
GO

-- create the certificate login to assign server permissions
CREATE LOGIN MasterDatabaseUserCertificateLogin
    FROM CERTIFICATE MasterDatabaseUserCertificate;
GO

-- create the certificate user to assign database permissions
CREATE USER MasterDatabaseUserCertificateUser
    FOR LOGIN MasterDatabaseUserCertificateLogin;
GO

--grant server permissions
GRANT VIEW SERVER STATE TO MasterDatabaseUserCertificateLogin;
GO

--grant database permissions
GRANT SELECT ON sys.dm_exec_query_stats TO MasterDatabaseUserCertificateUser;
GO

--export certificate to file
BACKUP CERTIFICATE MasterDatabaseUserCertificate
    TO FILE = 'C:\temp\YourCertificate.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\MasterDatabaseUserCertificate.pvk' ,
        ENCRYPTION BY PASSWORD = 'ksfd78#$%@8ks1@b@',
        DECRYPTION BY PASSWORD = 'ksfd78#$%@8ks1@b@');
GO

USE MyDatabase;
GO

-- import the certificate from file
CREATE CERTIFICATE MasterDatabaseUserCertificate
    FROM FILE = 'C:\temp\YourCertificate.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\MasterDatabaseUserCertificate.pvk' ,
        ENCRYPTION BY PASSWORD = 'ksfd78#$%@8ks1@b@',
        DECRYPTION BY PASSWORD = 'ksfd78#$%@8ks1@b@');
GO

-- sign procedure with certificate
ADD SIGNATURE TO dbo.usp_MySPROC BY CERTIFICATE MasterDatabaseUserCertificate
    WITH PASSWORD = 'ksfd78#$%@8ks1@b@';
GO