MCITP

MCITP

Thursday, October 11, 2012

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



2 comments:

  1. This is the perfect site for everyone who wants to find out about this topic.
    You realize so much its almost tough to argue with you (not
    that I actually will need to…HaHa). You definitely
    put a brand new spin on a subject that has been written about for ages.

    Excellent stuff, just wonderful!

    Feel free to surf to my site :: Green coffee Max cleanse
    Scam (shizzlaz.eu)

    ReplyDelete
  2. Cool Script ,one suggestion though ,surround the "?" in the "use ?" statement with square brackets to avoid issues when dealing with database names that has hypens in them "USE [?] "

    ReplyDelete

Followers