SQL Tip: Read + Write (or any) role on databases, all existing and new ones

Trying to avoid the double hop issue which configuring linked servers, I opted for local users.

After setting them up I asked the developer who requested this as to which databases he wanted to be read across the servers / instances and his answers was all now and in future.

Now for the requirement for all it was a case of creating a cursor and looping through it execute the right TSQL but for the future created databases I wondered should I create a DDL trigger if the SQL allows it?

Until I remember that the Model database’s purpose is to serve as the template for the new databases that get created and all the properties and settings that are not explicitly overwritten are inherited from this appropriately named template or the system database called Model.

Therefore I included Model database in my script and doing so will apply the user and its role assignment on Model explicitly therefore on any database created in the future.

Heres the script.

Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE @UserName VARCHAR(100)
-- User account
set @UserName = 'USER1LNKDSRVRUSER'

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM MASTER.sys.databases
WHERE name NOT IN ('master','msdb','tempdb','distribution') and [is_read_only]=0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @statement = 'use ['+ @dbname +'];'+ 'CREATE USER [' + @UserName + ']
FOR LOGIN [' + @UserName + ']; EXEC sp_addrolemember N''db_datareader'',
[' + @UserName + '];EXEC sp_addrolemember N''db_datawriter'', [' + @UserName + ']'

print @statement
exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Advertisements
About

IT professional with wider interest in technology

Tagged with:
Posted in SQL Server, tsql, Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: