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