有时我不得不从我们的生产SQL服务器恢复数据库到测试SQL实例。当数据库已经恢复后,我们手动为恢复的数据库恢复正确的访问权限(例如DB所有者/读取者/编写者)。这个过程相当有效,除了需要在恢复之前手动截屏权限,然后从拍摄的图像中重新应用它们。
是否有一种简单的方法可以使用T-SQL来存储用户在数据库还原之前的当前权限,并在恢复完成后重新应用这些相同的权限?
有时我不得不从我们的生产SQL服务器恢复数据库到测试SQL实例。当数据库已经恢复后,我们手动为恢复的数据库恢复正确的访问权限(例如DB所有者/读取者/编写者)。这个过程相当有效,除了需要在恢复之前手动截屏权限,然后从拍摄的图像中重新应用它们。
是否有一种简单的方法可以使用T-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
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
有一个非常有用的函数: 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
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
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
你遇到的问题被称为孤立用户。
这是我过去使用过的脚本(我根据记忆组合了这个脚本,因此你应该仔细验证):
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;