como criar um usuário SQL tendo acesso de gravação de leitura aos dbs existentes e dbs recém-criados

Question

Como configurar um usuário privilegiado de gravação de leitura no SQL Server para que esse usuário tenha acesso de gravação de leitura ao banco de dados recém-criado automaticamente. Isso deve ser aplicado a todos os bancos de dados de usuários - incluindo aqueles já criados ou recém-criados

    

Answer 1

Por acaso tenho isso por aí e pode atender às suas necessidades.

Declare
    @username sysname,
    @password varchar(255),
    @SQL nvarchar(max),
    @RowsToProcess int,
    @CurrentRow int 

set @username = 'youruser'
set @password = 'theirpassword'


    SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''' + @password + ''''
    EXECUTE(@SQL);

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), sqlcmd nvarchar(max) )  

insert into #maintenancetemp    
SELECT 'USE ' + QUOTENAME(NAME) + '; 
        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @username + ''') CREATE USER ' + QUOTENAME(@username)
       + ' FOR LOGIN ' + QUOTENAME(@username)
       + ' WITH DEFAULT_SCHEMA=[dbo];
    EXEC sys.sp_addrolemember ''db_datareader'',''' + @username + ''';
    EXEC sys.sp_addrolemember ''db_datawriter'', ''' + @username + ''''
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 


SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT @SQL = sqlcmd FROM #maintenancetemp WHERE RowID=@CurrentRow
        EXEC SP_EXECUTESQL @SQL
        --print @SQL                
END

drop table #maintenancetemp

Isso não ajuda com os bancos de dados recém-criados, mas você também pode executar um trabalho noturno para localizá-los:

Declare
    @username sysname,
    @SQL nvarchar(max),
    @RowsToProcess int,
    @CurrentRow int 

set @username = 'youruser'


CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), sqlcmd nvarchar(max) )  

insert into #maintenancetemp    
SELECT 'USE ' + QUOTENAME(NAME) + '; 
        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @username + ''') CREATE USER ' + QUOTENAME(@username)
       + ' FOR LOGIN ' + QUOTENAME(@username)
       + ' WITH DEFAULT_SCHEMA=[dbo];
    EXEC sys.sp_addrolemember ''db_datareader'',''' + @username + ''';
    EXEC sys.sp_addrolemember ''db_datawriter'', ''' + @username + ''''
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 
       AND create_date > dateadd(D, -1, GETDATE())


SELECT @RowsToProcess = COUNT(*) from #maintenancetemp

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT @SQL = sqlcmd FROM #maintenancetemp WHERE RowID=@CurrentRow
        EXEC SP_EXECUTESQL @SQL             
END

drop table #maintenancetemp

    

Answer 2

Isso você pode usar como um começo -

EXEC master..sp_MSForeachdb '
USE [?]
    IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempDB''  
    BEGIN
    print ''?''
    IF EXISTS (SELECT name FROM sysusers WHERE name = ''domainname\someuser'') DROP USER [domainname\someuser]
    CREATE USER [domain\someuser] FOR LOGIN [domain\someuser]
    EXEC sp_addrolemember ''db_datareader'', ''domain\someuser''
    EXEC sp_addrolemember ''db_datawriter'', ''domain\someuser''

end
'