主页 / dba / 问题 / 144096
Accepted
QWE
Asked: 2016-07-17 16:21:10 +0800 CST

一次将 db_owner 分配给所有用户

  • 772

我需要将 db_owner 分配给实例中的所有用户。实例中有几百个登录和相同数量的数据库。我希望每个用户都能在该用户所在的数据库中获取 db_owner 。我知道如何为个人用户执行此操作，但我不知道如何同时为所有用户执行此操作。

我很清楚将 db_owner 提供给所有人并不是一个好主意，但是……我们正在安装一个非常具体的应用程序。每个想要使用此应用程序的用户都需要 db_owner。我们已经尝试过解决方法，但是应用程序本质上是相当原始的，所以在更改应用程序代码之前，我们将不得不容忍疯狂数量的 db_owners。无论如何，这个特定实例上没有敏感数据，所以我们得到了管理层的批准。

sql-server users

3 个回答

  1. Best Answer

    我会使用两个游标。第一个运行所有数据库 - 除了 sys dbs。对于每个数据库，将获取登录名并将其添加到 db_owner 角色。

    如果您有数百个数据库/登录名，我会将其编写为带有一些事务处理和/或日志记录的数据库，如果在执行时发生崩溃，您可以在崩溃点继续。

    我无法测试下面的代码，这应该是一种如何开发自己的 proc 的方法

    Use master
GO

DECLARE @dbname VARCHAR(50)          
DECLARE @SQL_statement varchar(max)
DECLARE @chng_role NVARCHAR(MAX)


DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR    
  SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')    
OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @dbname    
WHILE @@FETCH_STATUS = 0    
 BEGIN 

    SET @SQL_statement = 'Declare  users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')'
    EXEC sp_executesql @sqlstatement
    OPEN users_cursor
    FETCH NEXT FROM users_cursor INTO @UserName
    WHILE @@FETCH_STATUS = 0
        BEGIN       
            SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + ''''
            EXECUTE sp_executesql @chng_role 
            FETCH NEXT FROM users_cursor INTO @UserName
        END
    CLOSE users_cursor
    DEALLOCATE users_cursor

 FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor
    • 6

  2. 我首先要说这是一个坏、坏、坏的主意。实例上的每个人都需要删除他们想要的任何数据库的能力吗？（可能没有备份）或截断表？或加密/解密列？等 DB_OWNER属于行政许可，需要严格控制。老实说，我反对DB_OWNER除 DBA 之外您不打算提供的任何人SYSADMIN。话虽如此，我喜欢知道如何做事，所以这里有一个稍微简单的处理方法。

    首先，我正在使用CONTROL database权限。这大致相当于DB_OWNER主要区别在于您不能将成员添加到DB_OWNER角色中。接下来，我将其授予PUBLIC角色。每个人都有PUBLIC，所以每个人都会一举获得许可。这样你只需要一个循环。仅供参考，我在此演示中使用 sp_msforeachdb，即使您不应该使用它。对于实际代码，请使用Aaron Bertrand 的替换代码。

    EXEC sp_msforeachdb 'USE [?]; GRANT CONTROL ON database::[?] TO public'

    这将涵盖所有新 ID 和新数据库（权限也在模型中授予）。最好的一点是您可以同样轻松地逆转它。

    如果您出于政治原因被迫这样做，那么我会要求以书面形式为您免除将要发生的不可避免的问题。

    我在这里谈到不是系统管理员。我意识到它是 sysadmin 而不是 db_owner 但同样的论点适用。

    以及此处的管理主体、角色和权限。

    • 6

  3. 如果可以使用 PowerShell，这里有一个更简单的解决方案（两行）

    #please replace: 
#<machine_name> to your proper server name
#<instance_name> to your sql instance name, if your sql instance is a default instance, using DEFAULT for <instance_name>

Import-Module sqlps -DisableNameChecking; 
dir SQLSERVER:\SQL\<machine_name>\<instance_name>\databases | % {$_.users } | ? {(-not $_.IsSystemObject)} | % {$_.addtorole('db_owner')}

    使用 PowerShell 的美妙之处在于，您可以在笔记本电脑上针对所有 sql 服务器（您拥有适当的权限）运行此行。

    注意：我同意 Kenneth Fisher 的评论，即授予每个人 db_owner 特权不是一个好主意，除非它真的很合理。

    • 2

相关问题