how to create a SQL user having read write access to the existing dbs and newly created dbs

Question

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

You just have to set global permissions for this user for select, delete and insert. Global permissions aren't database specific. Be aware that the user then can read ANY database, including structural ones. Can you please specify the sql-software you're using? (aka MS-SQL, Oracle, MySQL, SQLite, Postgres ......) — Broco, Sep 07 '16 at 10:49
Oh, ok, I'm not an expert on MS SQL but if I remember correctly there is no such thing as a global privilege. You could hover try `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). — Broco, Sep 07 '16 at 12:41

score 2 · Answer 1 · edited Sep 14 '16 at 13:04

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

score 1 · Answer 2 · edited Sep 14 '16 at 12:40

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
'

how to create a SQL user having read write access to the existing dbs and newly created dbs

2 Answers2