How to setup a read write privileged user in SQL Server so that that user has read write access to the newly created database automatically. This should be apply to all user databases - including those already created or just newly created ones
2 Answers
I happen to have this lying around and it might suit your needs.
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
That won't help with the newly created databases, but you can also run a nightly job to catch those:
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
This you may could use as a start -
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
'
sp_MSforeachdb
to go through all databases and grant rights to the user on every database (be sure to check that the ID is > 4 to skip system databases).