Script to grant database level role to a login on all user database

Below script will grant db_owner role to a newly created login on all user databases. We can use the same script to grant any database level role to the login on all user database.

Script 1 :- For Windows Login

create login loginname from windows

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '

EXEC sp_MSforeachdb @command

DECLARE @command1 varchar(1000)

Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'

EXEC sp_MSforeachdb @command1


For SQL Login

create login loginname with password = ' Enter the password for the login'

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user username for login loginname'') END '

EXEC sp_MSforeachdb @command

DECLARE @command1 varchar(1000)

Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'

EXEC sp_MSforeachdb @command1