Add Users to Database Script

The following script will add users to a database by first creating a logon for the Database Server then add user entries for each of the databases you want to give access to. Also specified are the role members for each database.

Add User Using Windows Authentication

[cc lang=”sql”]
— Create user windows Authentication
CREATE LOGIN [YourDomainNameJohnJacobs] FROM WINDOWS
WITH DEFAULT_DATABASE = [YourDatabaseHere];
GO
— Add User to first database
USE YourDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs];
EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’
— Add User to second database
USE YourSecondDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN [YourDomainNameJohnJacobs];
EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’
[/cc]

Add User Using SQL Authentication

[cc lang=”sql”]
— Create user for SQL Authentication
CREATE LOGIN JohnJacobs WITH PASSWORD = ‘JinGleHeimerSchmidt’
,DEFAULT_DATABASE = [YourDatabaseHere]
GO
— Add User to first database
USE YourDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN JohnJacobs;
EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’
GO
— Add User to second database
USE YourSecondDatabaseHere;
CREATE USER JohnJacobs FOR LOGIN JohnJacobs;
EXEC sp_addrolemember ‘db_datareader’, ‘JohnJacobs’
EXEC sp_addrolemember ‘db_datawriter’, ‘JohnJacobs’
[/cc]

To automate the creation use the script below. It will print out the statements so you can copy and paste them into SSMS.

[cc lang=”sql”]
SET NOCOUNT ON;

DECLARE @UserString VARCHAR(8000)
DECLARE @DatabaseString VARCHAR(8000)
DECLARE @DefaultDatabase VARCHAR(255)
DECLARE @RolesString VARCHAR(8000)
DECLARE @delimiter CHAR(1)

SET @UserString = ‘[yourdomainyouruser],[yourdomainyouruser2]’
SET @DatabaseString = ‘[AdventureWorks2012],[ReportServer]’
SET @DefaultDatabase = ‘[AdventureWorks2012]’
SET @RolesString = ‘db_datareader,db_datawriter’
SET @delimiter = ‘,’
BEGIN TRY
DROP TABLE #Users
DROP TABLE #Databases
DROP TABLE #Roles
END TRY BEGIN CATCH END CATCH

;WITH Substr(num, firstchar, lastchar) AS (
SELECT 1, 1, CHARINDEX(@delimiter, @UserString)
UNION ALL
SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @UserString, lastchar + 1)
FROM Substr
WHERE lastchar > 0
)
SELECT
num,
UserName = SUBSTRING(@UserString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
INTO #Users
FROM Substr

;WITH Substr(num, firstchar, lastchar) AS (
SELECT 1, 1, CHARINDEX(@delimiter, @DatabaseString)
UNION ALL
SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @DatabaseString, lastchar + 1)
FROM Substr
WHERE lastchar > 0
)
SELECT
num,
DatabaseName = SUBSTRING(@DatabaseString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
INTO #Databases
FROM Substr

;WITH Substr(num, firstchar, lastchar) AS (
SELECT 1, 1, CHARINDEX(@delimiter, @RolesString)
UNION ALL
SELECT num + 1, lastchar + 1, CHARINDEX(@delimiter, @RolesString, lastchar + 1)
FROM Substr
WHERE lastchar > 0
)
SELECT
num,
RoleName = SUBSTRING(@RolesString, firstchar, CASE WHEN lastchar > 0 THEN lastchar-firstchar ELSE 8000 END)
INTO #Roles
FROM Substr

DECLARE @NumUsers INT
DECLARE @NumDBs INT
DECLARE @NumRoles INT
DECLARE @UserIter INT
DECLARE @DBIter INT
DECLARE @RoleIter INT
DECLARE @UserName VARCHAR(255)
DECLARE @RoleUserName VARCHAR(255)
DECLARE @DBName VARCHAR(255)
DECLARE @RoleName VARCHAR(255)
DECLARE @SQL VARCHAR(MAX)

SET @NumUsers = (SELECT MAX(num) FROM #Users)
SET @NumDBs = (SELECT MAX(num) FROM #Databases)
SET @NumRoles = (SELECT MAX(num) FROM #Roles)
SET @UserIter = 1
SET @SQL = ”

WHILE @UserIter <= @NumUsers BEGIN SET @DBIter = 1 SET @UserName = (SELECT UserName FROM #Users WHERE num = @UserIter) --SET @SQL = 'CREATE LOGIN ' + @UserName + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + @DefaultDatabase SET @SQL = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD=N''mypassword'' MUST_CHANGE, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON' PRINT (@SQL) SET @UserIter = @UserIter + 1 -- Add user to the databases WHILE @DBIter <= @NumDBs BEGIN SET @RoleIter = 1 SET @DBName = (SELECT DatabaseName FROM #Databases WHERE num = @DBIter) SET @SQL = 'USE ' + @DBName + '; CREATE USER ' + @UserName + ' FOR LOGIN ' + @UserName PRINT (@SQL) SET @DBIter = @DBIter + 1 WHILE @RoleIter <= @NumRoles BEGIN SET @RoleName = (SELECT RoleName FROM #Roles WHERE num = @RoleIter) -- Must remove brackets for addrolemember procedure SET @RoleUserName = REPLACE(REPLACE(@UserName, '[', ''), ']', '') SET @SQL = 'USE ' + @DBName + '; EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @RoleUserName + '''' PRINT (@SQL) SET @RoleIter = @RoleIter + 1 END END END [/cc]

20 comments
Julienne 24 Mar 2018 at 5:30 am

Awesome post.cheap nfl jerseys

Reta 15 Mar 2018 at 2:34 am

Free shipping site of all cheap green bay packers jerseys products,more items bigger discount,hurry
to collect cheap amari cooper jersey

Antoinette 08 Mar 2018 at 5:19 am

I am now not sure where you’re getting your information, but good
topic. I needs to spend a while finding out much more or working
out more. Thanks for great information I was
searching for this information for my mission.p3148

Lawerence 06 Mar 2018 at 2:49 pm

What is size of cheap Deone Bucannon jerseys may have?
Know more about here – cheap D.J. Swearinger jerseys

Jay 19 Feb 2018 at 2:41 pm

Where to buy Cheap Bears Jerseys? Come here. Wholesale Browns
Jerseys

Veola 18 Feb 2018 at 1:24 pm

What team you support? Buy Cheap New York Knicks Jerseys and save more now.
Wholesale Tennessee Titans Jerseys

Justina 15 Feb 2018 at 11:54 pm

Youth,mens,womens Wholesale Panthers Jerseys online,find authentic quality Wholesale Chicago
Bears Jerseys

Josette 07 Feb 2018 at 4:30 am

Great place to order official baseball jerseys by Paypal.
cheap nhl hockey jerseys china

Ingrid 07 Feb 2018 at 4:02 am

Elite/throwback/authentic/replica cheap gilbert rugby balls for you,access
our site by click: Cheap Baltimore Ravens Jerseys

Wanda 15 Nov 2017 at 12:19 am

What is size of cheap T. J. Jones jerseys may have?
Know more about here – toddler hockey jerseys

Ashley 20 Jun 2017 at 3:23 pm

What is size of usa jersey wholesale may have? Know more about here – super saturday nfl
shop promotional codes

Billy Batts 16 Jan 2013 at 10:16 am

Excellent work!

Because I needed that feature I added DROP LOGIN and DROP USER – works like a charm.

suresh kumar reddy 12 May 2012 at 1:51 pm

Excellent script

i used and find one logical error that the roles creating for first database in the list not for the rest.

i corrected the logic with putting SET @RoleIter = 1 statement in the right place and works fine now.

here is the code:

WHILE @UserIter <= @NumUsers
BEGIN
SET @DBIter = 1
SET @UserName = (SELECT UserName FROM #Users WHERE num = @UserIter)
–SET @SQL = 'CREATE LOGIN ' + @UserName + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + @DefaultDatabase
SET @SQL = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD=N''[email protected]'' MUST_CHANGE, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON'
PRINT (@SQL)
SET @UserIter = @UserIter + 1
— Add user to the databases
WHILE @DBIter <= @NumDBs
BEGIN
SET @RoleIter = 1
SET @DBName = (SELECT DatabaseName FROM #Databases WHERE num = @DBIter)
SET @SQL = 'USE ' + @DBName + '; CREATE USER ' + @UserName + ' FOR LOGIN ' + @UserName
PRINT (@SQL)
SET @DBIter = @DBIter + 1
WHILE @RoleIter <= @NumRoles
BEGIN
SET @RoleName = (SELECT RoleName FROM #Roles WHERE num = @RoleIter)
— Must remove brackets for addrolemember procedure
SET @RoleUserName = REPLACE(REPLACE(@UserName, '[', ''), ']', '')
SET @SQL = 'USE ' + @DBName + '; EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @RoleUserName + ''''
PRINT (@SQL)
SET @RoleIter = @RoleIter + 1
END
END
END

Thanks

Derek Dieter 29 Oct 2012 at 1:16 am

Thank you Suresh

Banutharan 11 Mar 2012 at 9:05 am

Excellent code and appreciated your time.

What if DBA wanted to rollback login creation and revoking roles?

Thanks in advance.
Banutharan.

RMC 22 Nov 2011 at 7:40 am

Thanks for this post – really helped me to solve my problem!

Anil Kale 22 Jul 2011 at 2:15 am

This article is very useful for me to create – Request for Change (RFC) ->which will assign the role to user through script.

Nitesh 05 Aug 2010 at 6:20 am

Great post. helped me alot after I was stuck for days

Derek Dieter 07 Aug 2010 at 9:07 pm

Great, glad it helped Nitesh.

Smarty 15 Jul 2010 at 6:41 pm

Very handy page – thanks!

Featured Articles

 Site Author

  • Thanks for visiting!