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

-- 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'

Add User Using SQL Authentication

-- 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'

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

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

9 comments
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!