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


1

我有一个循环遍历数据库中所有对象的过程,并为它们分配适当的权限。我想知道是否有更好的方法来做到这一点?我使用模型数据库来创建新数据库,因此每次创建新数据库时都必须运行它。下面是它的外观(注意:从一开始就缺少一个块,它会丢弃所有用户并重新创建必要的用户;这是必要的,因为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

这个脚本继续为数据库中的所有视图,函数等做类似的事情。任何加速这件事的想法还是有更好的方法来做到这一点?

6

单行以在模式级别为角色提供权限

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

第二行是将用户添加到角色中

EXEC sp_addrolemember 'SomeRole', 'whatever user'

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

原因,您应该只设置一次权限:

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

如您所见,迁移或还原数据库可能会在直接分配给用户时失去对象权限。那么为什么要把自己放在那个位置?

您也可以CREATE LOGIN with a SID因此它在您的所有服务器上都是相同的,您也不会获得孤立用户。

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

就个人而言,最后,我会说这是“需要拥有”的不良做法,而不是“毯子做任何事情”

  0

所以类似:'IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE [type] ='R'和[name] ='ReadOnlyUsers')''CREATE ROLE [ReadOnlyUsers]''EXEC sp_addrolemember'db_datareader','ReadOnlyUser' ''GRANT?(选择?或?)ON SCHEMA :: dbo TO [ReadOnlyUsers]''GO''EXEC sp_addrolemember'ReadOnlyUsers','ReadOnlyUser''如果所有对象都是如此区分EXEC,UPDATE,DELETE等但在dbo下呢?例如,ReadOnlyUser仍然需要EXEC存储过程。 20 9月. 112011-09-20 15:27:51

  0

@David George:无论对象如何,架构权限都会自动应用:因此proc获取EXEC,表格获得S/I/U/D.你的GRANT将是GRANT EXEX,SELECT,UPDATE等...... 20 9月. 112011-09-20 16:33:55

  0

所以我会在SCHEMA :: dbo上运行'GRANT EXEC,SELECT,UPDATE,INSERT,DELETE TO [ReadWriteUsers]'这会允许该用户权限进行DROP过程吗? 20 9月. 112011-09-20 18:54:03

  0

不,他们需要ALTER TABLE等 20 9月. 112011-09-20 22:29:39