主页 / dba / 问题 / 5759
Accepted
David George
Asked: 2011-09-15 07:31:03 +0800 CST

为 SQL 用户设置所有对象的权限

  • 772

我有一个循环遍历数据库中所有对象的过程，并为它们分配适当的权限给该对象。我想知道是否有更好的方法来做到这一点？我使用模型数据库来创建新数据库，因此每次创建新数据库时都必须运行它。这是它的样子（注意：从一开始就缺少一个块，它会删除所有用户并重新创建必要的用户；这是必要的，因为 SID 发生了变化）：

CREATE PROCEDURE usp_SetPermissions 
AS 
 BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects 
(
   ID INT IDENTITY(1,1),
   ObjectName NVARCHAR(128),
   Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
 BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
    EXEC sp_sqlexec @Sql

    -- Update object completion 
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
     BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql

    -- Update object completion 
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

该脚本继续对数据库中的所有视图、函数等执行类似的操作。有什么加快这件事的想法，或者有更好的方法吗？

sql-server database-design

1 个回答

  1. Best Answer

    在架构级别向角色授予权限的单行

    GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

    第二行将用户添加到角色

    EXEC sp_addrolemember 'SomeRole', 'whatever user'

    并在模型中执行此操作，以便所有新数据库都继承。

    原因，您应该只设置一次权限：

    • 模式是对象的容器。
    • 新对象从架构继承权限
    • 角色是用户的容器
    • 新用户被添加到角色并继承

    如您所见，迁移或恢复数据库在直接分配给用户时可能会丢失对象权限。那么为什么要把自己放在那个位置呢？

    您还可以使用 SID 创建登录，因此它在您的所有服务器上都是相同的，并且您也不会获得孤立的用户。

    如果您问了正确的问题，我们可以为您节省一些编码......

    就个人而言，最后，我会说这是“需要”而不是“一揽子做任何事情”的坏习惯

    • 6

相关问题