1

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

3
  • 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 7, 2016 at 10:49
  • Broco, I am using SQL Server 2012 Enterprise Edition Sep 7, 2016 at 11:53
  • 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 7, 2016 at 12:41

2 Answers 2

2

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
1

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
'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .