主页 / dba / 问题 / 7709
Accepted
Kyle Brandt
Asked: 2011-11-10 11:50:55 +0800 CST

在 SQL Server 2008 R2 中授予读取所有权限的最佳方法？

  • 772

向用户（在这种情况下为 AD 组）授予对所有当前和未来数据库的读取访问权限的最佳方法是什么？

目前我有一个不稳定的预定作业，它使用（将开始调试以下内容，但也许我做错了）：

sp_msforeachdb '
USE [?]
CREATE USER [MY_DOMAIN\SQL-READ-ALL] FOR LOGIN [MY_DOMAIN\SQL-READ-ALL]
USE [?]
EXEC sp_addrolemember N''db_datareader'', N''MY_DOMAIN\SQL-READ-ALL''
'

也许有更好的方法？

sql-server-2008-r2

3 个回答

  1. Best Answer

    对于未来的数据库，请这样做，因为所有数据库都是从model

    USE model
EXEC sp_addrolemember N'db_datareader', N'MY_DOMAIN\SQL-READ-ALL'

    对于现有数据库，只需运行上面的内容，一次

    • 10

  2. 关于恢复：您可以为AUDIT_BACKUP_RESTORE事件设置事件通知。不适合胆小的人，因为编写异步事件通知并不容易，但它可以完成您要求的工作。请参阅实现事件通知。

    • 3

  3. 另一种选择是创建一个按计划运行的 SQL Server 代理作业，以便为所有数据库执行 SQL。

    您还可以将代码包装在存储过程中，您可以在模型数据库或“实用程序”数据库中创建该存储过程。

    下面是一种可能的实现（假设登录名和用户名相同，但可以修改以处理不同的登录名/用户名）：

    CREATE PROCEDURE [dbo].[usp_CreateLoginAndUserDbDataReader]
    @LoginName NVARCHAR(50),
    @Password  NVARCHAR(50),
    @DbName NVARCHAR(128)

AS
BEGIN
    DECLARE @SQL nvarchar(1000)

    IF NULLIF(@LoginName, '') IS NULL
        OR NULLIF(@Password, '') IS NULL
        OR NULLIF(@DbName, '') IS NULL
        THROW 70000, 'You must specify a value for @LoginName, @Password, and @DbName', 1

    IF NOT EXISTS (SELECT name
        FROM master.sys.server_principals
        WHERE name = @LoginName)
    BEGIN
        PRINT '@loginname = ' + COALESCE( @LoginName, 'NULL' )
        SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = ''' + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
        PRINT @SQL
        EXECUTE (@SQL);
    END

    SET @SQL = N'USE [' + @DbName + ']; 
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''' + @LoginName + ''')
    BEGIN
        CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']
    END';
    PRINT @SQL
    EXECUTE (@SQL);

    SET @SQL = 'USE [' + @DbName + ']; ALTER ROLE [db_datareader] ADD MEMBER [' + @LoginName + ']';
    PRINT @SQL
    EXECUTE (@SQL);

    RETURN 0
END

    从计划每天运行或按所需计划运行的 SQL Server 代理作业，您可以像这样调用上面的代码：

    sp_msforeachdb '
USE *DatabaseWhereStoredProcedureResides*
EXEC [usp_CreateLoginAndUserDbDataReader] ''*LoginName*'', ''*UserName*'', ''?''
'
    • 0

相关问题