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 Servername\LoginName from windows

DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')
BEGIN USE ? EXEC(''create user Servername\LoginName for login Servername\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'', ''Servername\LoginName'' END'

EXEC sp_MSforeachdb @command1


E.g. :-

For SQL Logins :-



create login loginname with password ='Give the password for SQL 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


If you want to create the same login on mutiple server and assign it db_owner rights on all user databases then save the required script as dbownerscript.sql in C drive under SQL folder and open the CMD prompt.

On the cmd prompt use the below command for each server and it will execute the script the on that particular server.

sqlcmd -S Servername\InstanceName -i C:\sql\dbownerscript.sql