Add login to same database roles in multiple databases can be time consuming. You find this useful when you need to add some login to instance. Especially when application using it should have same permissions on multiple databases. You can definitely use Management studio and click through all specified databases, then click on every database role checkbox. Yes this is pretty easy. But imagine you have to grant same roles to 10 databases. This is boring, time consuming and you can forget/mismatch some role or database in list.
Login properties in Management Studio – after execution
TIP: You can modify this script for granting explicit permissions as well, just change @TSQL variable definition starting on line 20.
DECLARE @Databases TABLE (DatabaseName SYSNAME) DECLARE @TSQL NVARCHAR(MAX) DECLARE @DatabasesString NVARCHAR(MAX) DECLARE @LoginName NVARCHAR(256) -- specify login name (must exists already) SET @LoginName = N'' -- specify databases for granting permissions INSERT INTO @Databases (DatabaseName) VALUES ('AdventureWorks2017'), ('DBBackupsChecking'), ('ReportServer'), ('ReportServerTempDB') -- create one comma separarated string from databases table SELECT @DatabasesString = COALESCE(@DatabasesString + ', ', '') + '''' + DatabaseName + '''' FROM @Databases -- prepare command to be executed in every specified database (you have to adjust to fit roles/permissions required in you case) SET @TSQL = N' IF ''?'' IN (' + @DatabasesString + ') BEGIN USE ? PRINT ''Processing database ['' + DB_NAME() + '']''; IF DATABASE_PRINCIPAL_ID(''' + @LoginName + ''') IS NULL BEGIN PRINT '' - creating user [' + @LoginName + ']''; CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']; END ELSE BEGIN PRINT '' - user [' + @LoginName + '] already exists''; END PRINT '' - granting permissions for user [' + @LoginName + ']''; ALTER ROLE [db_datareader] ADD MEMBER [' + @LoginName + ']; ALTER ROLE [db_datawriter] ADD MEMBER [' + @LoginName + ']; ALTER ROLE [db_ddladmin] ADD MEMBER [' + @LoginName + ']; END ' -- fire execution for each database in instance EXEC sp_MSforeachdb @TSQL
Results
Script messages after execution:
(4 rows affected) Processing database [ReportServer] - creating user [Test] - granting permissions for user [Test] Processing database [ReportServerTempDB] - creating user [Test] - granting permissions for user [Test] Processing database [DBBackupsChecking] - creating user [Test] - granting permissions for user [Test] Processing database [AdventureWorks2017] - creating user [Test] - granting permissions for user [Test]