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]