Вопросы Теги

как создать пользователя SQL, имеющего доступ для чтения и записи к существующим базам данных и вновь созданным базам данных

Как настроить привилегированного пользователя для чтения и записи в SQL Server, чтобы этот пользователь автоматически имел доступ для чтения и записи к вновь созданной базе данных. Это должно применяться ко всем пользовательским базам данных, включая уже созданные или только что созданные

1
задан 7 September 2016 в 15:38
2 ответа

Вот эта штука у меня есть, и она может удовлетворить ваши потребности. 

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

Это не поможет с недавно созданными базами данных, но вы также можете запускать ночное задание, чтобы их поймать: 

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
2
ответ дан 3 December 2019 в 18:33

Это вы можете использовать как начало - 

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
'
1
ответ дан 3 December 2019 в 18:33

Теги

Похожие вопросы