编写SQL Server权限脚本
编写SQL Server权限脚本
提问于 2010-01-01 05:34:37
我希望将我在存储过程和其他内容上设置的所有权限从我的开发数据库复制到我的生产数据库。通过SSMS GUI工具手动完成这些操作非常麻烦，更不用说容易出错了。
因此，我正在寻找一种方法，可以直接转储一个数据库中的权限集，并将这些相同的权限应用于另一个单独的数据库(可能具有相同的模式)。
回答 9
Stack Overflow用户
回答已采纳
发布于 2010-01-01 05:39:40
数据库的内置目录视图提供了执行此操作的信息。尝试此查询：
SELECT
(
dp.state_desc + ' ' +
dp.permission_name collate latin1_general_cs_as +
' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
' TO ' + '[' + dpr.name + ']'
) AS GRANT_STMT
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
WHERE dpr.name NOT IN ('public','guest')
-- AND o.name IN ('My_Procedure') -- Uncomment to filter to specific object(s)
-- AND dp.permission_name='EXECUTE' -- Uncomment to filter to just the EXECUTEs这将为数据库中的每个权限输出一组命令(GRANT/DENY)。在此基础上，您可以将它们复制并粘贴到另一个查询窗口中并执行，以生成与原始查询窗口相同的权限。例如：
GRANT EXECUTE ON [Exposed].[EmployeePunchoutReservationRetrieve] TO [CustomerAgentRole]
GRANT EXECUTE ON [Exposed].[EmployeePunchoutReservationStore] TO [CustomerAgentRole]
GRANT EXECUTE ON [Exposed].[EmployeePunchoutSendOrderLogStore] TO [CustomerAgentRole]
GRANT EXECUTE ON [Exposed].[EmployeeReportSubscriptions] TO [CustomerAgentRole]注意底线，注释掉了，那就是过滤permission_name。取消对该行的注释将导致查询只显示EXECUTE权限(即存储过程的权限)。
Stack Overflow用户
发布于 2013-06-14 22:39:39
您可以让SQL Server Management Studio为您执行此操作：
代码创建右键单击要导出代码权限的数据库选择'Tasks‘，然后选择'Generate Scripts...'
- Confirm
- you’
- 设置以下脚本选项：
- Script Create: FALSE
<>H111>脚本对象级权限：
- 选择要生成其权限的对象类型要生成其权限的对象要生成脚本的位置
这将生成一个为所有选定对象设置权限的脚本，但会抑制对象脚本本身。
这基于MS SQL 2008的对话框，所有其他脚本选项都与安装默认值保持不变。
Stack Overflow用户
发布于 2017-12-20 20:57:12
在https://stackoverflow.com/a/1987215/275388中提供的答案上进行扩展，该答案对于数据库/模式范围的权限和数据库用户类型是失败的，您可以使用：
SELECT
CASE
WHEN dp.class_desc = 'OBJECT_OR_COLUMN' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' ON ' + '[' + obj_sch.name + ']' + '.' + '[' + o.name + ']' +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'DATABASE' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'SCHEMA' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' ON SCHEMA :: ' + '[' + SCHEMA_NAME(dp.major_id) + ']' +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'TYPE' THEN
dp.state_desc + ' ' + dp.permission_name collate Latin1_General_CS_AS +
' ON TYPE :: [' + s_types.name + '].[' + t.name + ']'
+ ' TO [' + dpr.name + ']'
WHEN dp.class_desc = 'CERTIFICATE' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' TO ' + '[' + dpr.name + ']'
WHEN dp.class_desc = 'SYMMETRIC_KEYS' THEN
dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +
' TO ' + '[' + dpr.name + ']'
ELSE
'ERROR: Unhandled class_desc: ' + dp.class_desc
END
AS GRANT_STMT
FROM sys.database_permissions AS dp
JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
LEFT JOIN sys.objects AS o ON dp.major_id=o.object_id
LEFT JOIN sys.schemas AS obj_sch ON o.schema_id = obj_sch.schema_id
LEFT JOIN sys.types AS t ON dp.major_id = t.user_type_id
LEFT JOIN sys.schemas AS s_types ON t.schema_id = s_types.schema_id
WHERE
dpr.name NOT IN ('public','guest')
-- AND o.name IN ('My_Procedure') -- Uncomment to filter to specific object(s)
-- AND (o.name NOT IN ('My_Procedure') or o.name is null) -- Uncomment to filter out specific object(s), but include rows with no o.name (VIEW DEFINITION etc.)
-- AND dp.permission_name='EXECUTE' -- Uncomment to filter to just the EXECUTEs
-- AND dpr.name LIKE '%user_name%' -- Uncomment to filter to just matching users
ORDER BY dpr.name, dp.class_desc, dp.permission_name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1987190
复制相关文章
相似问题
腾讯云开发者
