Início / dba / Perguntas / 7709
Accepted
Kyle Brandt
Asked: 2011-11-10 11:50:55 +0800 CST

Melhor maneira de conceder permissão de leitura total no SQL Server 2008 R2?

  • 772

Qual é o melhor método para conceder acesso de leitura a todos os bancos de dados atuais e futuros a um usuário (AD Group, neste caso)?

Atualmente, tenho um trabalho agendado instável que faz isso usando (vou começar a depurar o seguinte, mas talvez eu esteja fazendo errado de qualquer maneira):

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''
'

Talvez haja uma maneira melhor?

sql-server-2008-r2

3 respostas

  1. Best Answer

    Para bancos de dados futuros , faça isso porque todos os bancos de dados são criados a partirmodel

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

    Para bancos de dados existentes, basta executar o que você tem acima, uma vez

    • 10

  2. Sobre a restauração: você pode configurar uma notificação de evento para o evento AUDIT_BACKUP_RESTORE . Não é para os fracos de coração, já que programar notificações de eventos assíncronos não é exatamente fácil , mas pode fazer o trabalho que você está pedindo. Consulte Implementando Notificações de Eventos .

    • 3

  3. Outra opção é criar um trabalho de agente do SQL Server que seja executado em um agendamento, para executar seu SQL para todos os bancos de dados.

    Você também pode agrupar seu código em um procedimento armazenado, que pode ser criado no banco de dados modelo ou em um banco de dados "Utilitários".

    Uma implementação possível está abaixo (isso assume que o nome de login e o nome de usuário serão os mesmos, mas podem ser modificados para lidar com nomes de login/usuário diferentes):

    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

    A partir de um trabalho do SQL Server Agent agendado para ser executado diariamente ou em um agendamento desejado, você pode chamar o acima desta forma:

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

relate perguntas