T-SQL脚本当前用户数据库权限

Question

有时我不得不从我们的生产SQL服务器恢复数据库到测试SQL实例。当数据库已经恢复后，我们手动为恢复的数据库恢复正确的访问权限（例如DB所有者/读取者/编写者）。这个过程相当有效，除了需要在恢复之前手动截屏权限，然后从拍摄的图像中重新应用它们。

是否有一种简单的方法可以使用T-SQL来存储用户在数据库还原之前的当前权限，并在恢复完成后重新应用这些相同的权限？

- user2165609

Answer 1

您的问题的答案很可能是Jeff提供的那个，但Howard的脚本非常实用，我只添加了一列，该列生成TSQL语法与信息一起。您可以复制它并运行为SQL，以将权限“复制”到另一个数据库。

SELECT 
dp.permission_name collate latin1_general_cs_as    AS Permission,
t.TABLE_SCHEMA + '.' + o.name AS Object,
dpr.name AS Username
, 'GRANT ' + dp.permission_name collate latin1_general_cs_as 
    + ' ON ' 
    + t.TABLE_SCHEMA 
    + '.' 
    + o.name 
    + ' TO ' 
    +  dpr.name
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON  TABLE_NAME = o.name                
WHERE dpr.name NOT IN ('public','guest')
ORDER BY
   Permission, Object,Username

- Fabian

---

他说了权限，但从他接下来所说的内容来看，他实际上是指角色成员，而这个脚本并没有涉及到。 - Paul

这个很好，但对于存储过程不起作用。我不得不手动编写脚本，例如GRANT EXECUTE ON... - Guy Lowe

Answer 2

这是@Fabian编写的脚本的修改版本，以便我也可以为存储过程编写脚本权限。还添加了QUOTENAME，使适当的内容在括号中。

SELECT 
    dp.permission_name collate latin1_general_cs_as    AS Permission,
    t.TABLE_SCHEMA + '.' + o.name AS TableName,
    rt.ROUTINE_SCHEMA + '.' + o.name AS ProcedureName,
    dpr.name AS Username
    , 'GRANT ' + dp.permission_name collate latin1_general_cs_as 
        + ' ON ' 
        + QUOTENAME(CASE WHEN t.TABLE_SCHEMA IS NOT NULL THEN t.TABLE_SCHEMA ELSE rt.ROUTINE_SCHEMA END )
        + '.' 
        + QUOTENAME(o.name)
        + ' TO ' 
        +  QUOTENAME(dpr.name)
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
LEFT outer JOIN INFORMATION_SCHEMA.TABLES t ON  TABLE_NAME = o.name              
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES rt ON rt.ROUTINE_NAME = o.name
WHERE dpr.name NOT IN ('public','guest')
ORDER BY Permission, TableName, ProcedureName, Username

- cooldug000

Answer 3

有一个非常有用的函数: sys.fn_my_permissions(securable, 'securable_class') 它可以让您查看当前用户对指定对象的有效权限，因此我不知道是否可以从中简单地构建GRANT / DENY命令。我从未以这种方式使用过它。 在您的情况下，您将其作为另一个用户运行：

EXECUTE AS USER = '<username>';
GO
SELECT *
FROM fn_my_permissions(null, 'SERVER') 
GO

SELECT *
FROM fn_my_permissions('<DBNAME>', 'Database')
ORDER BY subentity_name, permission_name ;

REVERT;
GO

- AdamL

---

谢谢您的回复 :) 是的，这是一个非常有用的函数。您知道是否有一种方法可以以t-sql格式返回权限，以便通过运行返回的脚本轻松重新应用它们吗？ - JSpendley

Answer 4

SELECT 
dp.permission_name collate latin1_general_cs_as    AS Permission,
t.TABLE_SCHEMA + '.' + o.name AS Object,
dpr.name AS Username
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
INNER JOIN INFORMATION_SCHEMA.TABLES t
        ON  TABLE_NAME = o.name                
WHERE dpr.name NOT IN ('public','guest')
ORDER BY
  Permission, Object,Username

- Howard Rothenburg

Answer 5

将此与有关权限的答案相结合：

SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '') 
AS 'Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id

- Paul

Answer 6

你遇到的问题被称为孤立用户。

这是我过去使用过的脚本（我根据记忆组合了这个脚本，因此你应该仔细验证）：

create table #users (UserName sysname, UserSID varbinary(85))

Insert into #users
exec sp_change_users_login @Action='Report';

declare mycursor cursor for select * from #users;
open mycursor;

declare @UserName sysname;
declare @UserSID varbinary(85);

fetch next from mycursor
into @UserName, @UserSID

while @@FETCH_STATUS = 0
begin
    exec  sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName;
end
close mycursor;
deallocate mycursor;

这个脚本有一个重要的假设，即用户的登录名和数据库中的用户名匹配。如果不是这样，您将不得不更改发送到sp_change_users_login的@LoginName参数。

- Jeff Siver

---

非常感谢您的回复 :) 您的脚本很有效，但问题不是孤立的用户。我需要在数据库恢复之前编写用户权限的脚本。然后，我将使用该脚本重新应用记录的权限。我表达清楚了吗？ :/ - JSpendley