Dynamically create user from login on databases

Question

I have a table that contains a list of database names that were restored by a SQL Job. We currently have a SQL Job, which runs after the restore, that creates a user in each of these databases for a specific set login and adds the db_datareader role as follows:

---

USE Datebase1
GO
create user [Domain\jdoe] from login [Domain\jdoe];
exec sp_addrolemember 'db_datareader', 'Domain\jdoe';

USE Datebase2
GO
create user [Domain\jdoe] from login [Domain\jdoe];
exec sp_addrolemember 'db_datareader', 'Domain\jdoe';

USE Datebase3
GO
create user [Domain\jdoe] from login [Domain\jdoe];
exec sp_addrolemember 'db_datareader', 'Domain\jdoe';

---

This works but the the list of restored databases in the table varies based on each run of the SQL Job. This requires us to constantly edit the step in the SQL job with new names for the 'Use Database#' portion. for example, the next run of the SQL Job step might look like this:

---

USE Datebase1000 (HAVE TO EDIT THIS)
GO
create user [Domain\jdoe] from login [Domain\jdoe];
exec sp_addrolemember 'db_datareader', 'Domain\jdoe';

USE Datebase2000 (HAVE TO EDIT THIS)
GO
create user [Domain\jdoe] from login [Domain\jdoe];
exec sp_addrolemember 'db_datareader', 'Domain\jdoe';

USE Datebase3000 (HAVE TO EDIT THIS)
GO
create user [Domain\jdoe] from login [Domain\jdoe];
exec sp_addrolemember 'db_datareader', 'Domain\jdoe';

---

Assuming our login already exists on the server (Ex: Domain\Jdoe), how would we dynamically run the steps above for a variable list of databases?

Thanks

Answer 1

DECLARE @sql = nvarchar(MAX)
SELECT @sql = 'create user [Domain\jdoe] from login [Domain\jdoe];
ALTER ROLE db_datareader ADD MEMBER [Domain\jdoe]'

DECLARE @cur CURSOR,
        @db sysname,
        @sp_executesql nvarchar(200)

SET @cur = CURSOR STATIC FOR
    SELECT db FROM yourtable

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @db
   IF @@fetch_status <> 0

   SELECT @sp_executesql = quotename(@db) + '.sys.sp_executesql'
   EXEC @sp_executesql @sql
END

As I typed this directly into the query window, there may be typos, but hopefully you get the idea.

Answer 2

Hi MarkSanchez-9060,
Please also try this:

create proc sp_login_creation  
 @login varchar(100),  
 @dbname varchar(50)  
 as  
 begin  
  
        DECLARE @sqlcmd VARCHAR(MAX);  
         SET @sqlcmd = '  
             USE ' + QUOTENAME(@dbname) + ';  
             CREATE USER '+ QUOTENAME(@login) +' FOR LOGIN '+ QUOTENAME(@login) +';  
             ALTER ROLE [db_datareader] ADD MEMBER' + QUOTENAME(@login) +';  
             ';  
         EXEC(@sqlcmd);  
     END  

Hope this could help you.
Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.