I recently had to create a whole number of logins for a range of databases. So I developed this script that would loop through all the user databases on a server and create a login based on database name, create a random password for that login, assign the login to the appropriate database (user) and assign appropriate database roles.
It can be run multiple times and will only create logins/users that don’t exist. It will however apply the database roles to every user it is specified to create. (i.e it will apply to users it created in previous runs).
In addition the script creates a custom database role, to allow execute permissions for running stored procedures and functions.
The script will output username and passwords created
DECLARE @username varchar(50)
DECLARE @dbRole varchar(50)
DECLARE @vpassword varchar(8)
DECLARE @SQL varchar(max)
DECLARE @DatabaseName varchar(MAX)
DECLARE @output varchar(max)
SET @output = ''
DECLARE @uniquepassword uniqueidentifier
SET @uniquepassword = NEWID()
SET @dbRole = 'db_executor'
DECLARE my_cursor CURSOR FOR
SELECT CAST([Name] AS varchar(MAX)) AS databasename
FROM sys.sysdatabases
--only user databases
WHERE DBID>4 AND [NAME] NOT LIKE '$'
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @uniquepassword = NEWID()
SELECT @vpassword = LEFT(@uniquepassword, 8)
--Check if login exists, if not create login for server
IF NOT EXISTS(SELECT name FROM master.dbo.syslogins WHERE name = @DatabaseName)
BEGIN
SET @SQL = 'USE MASTER; CREATE LOGIN ' + @DatabaseName + ' WITH PASSWORD = ''' + @vpassword + ''', DEFAULT_DATABASE=[' + @DatabaseName + '], DEFAULT_LANGUAGE=[English], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
EXECUTE(@SQL);
SET @output = @output + CHAR(13) + CHAR(10) + 'LOGIN:' + CHAR(9) + CHAR(9) + @DatabaseName;
SET @output = @output + CHAR(13) + CHAR(10) + 'PASSWORD:' + CHAR(9) + @vpassword;
END
--Check if user exists, if not create user in database
SET @SQL = 'USE ' + @DatabaseName + ';
IF NOT EXISTS(SELECT ' + @DatabaseName + '.sys.database_principals.name FROM ' + @DatabaseName + '.sys.database_principals WHERE ' + @DatabaseName + '.sys.database_principals.name = ''' + @DatabaseName + ''')
BEGIN
USE ' + @DatabaseName + ';
CREATE USER ' + @DatabaseName + ' FOR LOGIN ' + @DatabaseName + '
END'
EXECUTE(@SQL);
--Check if db_executor role exists, if not create role in database and then assign execute permissions
SET @SQL = 'USE ' + @DatabaseName + ';
IF NOT EXISTS(SELECT 1 FROM ' + @DatabaseName + '.sys.database_principals WHERE ' + @DatabaseName + '.sys.database_principals.name = ''' + @dbRole + ''' AND [TYPE] = ''R'')
BEGIN
CREATE ROLE ' + @dbRole + ';
END
GRANT EXECUTE TO ' + @dbRole + ';'
EXECUTE(@SQL);
SET @SQL = 'USE ' + @DatabaseName + '; EXEC sp_addrolemember @rolename = ''db_datareader'' , @membername = ''' + @DatabaseName + '''';
EXECUTE(@SQL);
SET @SQL = 'USE ' + @DatabaseName + '; EXEC sp_addrolemember @rolename = ''db_datawriter'' , @membername = ''' + @DatabaseName + '''';
EXECUTE(@SQL);
SET @SQL = 'USE ' + @DatabaseName + '; EXEC sp_addrolemember @rolename = ''db_executor'' , @membername = ''' + @DatabaseName + '''';
EXECUTE(@SQL);
FETCH NEXT FROM my_cursor
INTO @DatabaseName
END
CLOSE my_cursor
DEALLOCATE my_cursor
/*This output statement will contain the usernames and passwords created for the users*/
SELECT @output
SQL Server- Creating Multiple logins at one time for multiple databases
