Create SQL Login with Powershell and T-SQL

If you ever have the need to create a number of logins and users for a specific database(s) programmatically, then the following scripts may be of use to you.

The first script leverages the strength of Powershell and can be extended for example to pull a number of usernames and passwords from a file and then use these to create the necessary logins. For our purposes today, I’ve decided to use a hardcoded username and password but it is nice to know that Powershell can do much more. The script is attached below:

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

$instanceName = "CHRISTOS-LAPTOP"
$loginName = "testUser"
$dbUserName = "testUser"
$password = "test123"
$databasenames = "TestDB1", "TestDB2"
$roleName = "db_owner"

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

# drop login if it exists
if ($server.Logins.Contains($loginName)) 
{   
	Write-Host("Deleting the existing login $loginName.")
   	$server.Logins[$loginName].Drop() 
}

$login = New-Object `
-TypeName Microsoft.SqlServer.Management.Smo.Login `
-ArgumentList $server, $loginName
$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
$login.PasswordExpirationEnabled = $false
$login.Create($password)
Write-Host("Login $loginName created successfully.")

foreach($databaseToMap in $databasenames)
{
	$database = $server.Databases[$databaseToMap]
	if ($database.Users[$dbUserName])
	{
		Write-Host("Dropping user $dbUserName on $database.")
	    $database.Users[$dbUserName].Drop()
	}

	$dbUser = New-Object `
	-TypeName Microsoft.SqlServer.Management.Smo.User `
	-ArgumentList $database, $dbUserName
	$dbUser.Login = $loginName
	$dbUser.Create()
	Write-Host("User $dbUser created successfully.")

	#assign database role for a new user
	$dbrole = $database.Roles[$roleName]
	$dbrole.AddMember($dbUserName)
	$dbrole.Alter()
	Write-Host("User $dbUser successfully added to $roleName role.")
}

The second script is written in T-SQL and performs the same exact operation as the previous PS script. The script is attached below:

DECLARE @login NVARCHAR(100) = 'TestUser';
DECLARE @user NVARCHAR(100) = 'TestUser';
DECLARE @password NVARCHAR(500) = 'Test123';
DECLARE @database NVARCHAR(100) = 'TestDB';
DECLARE @sqlString NVARCHAR(MAX);
DECLARE @sqlParams NVARCHAR(1000);

SET @sqlString = '
	USE [master];

	IF EXISTS (SELECT [name] from master.sys.server_principals Where [name] = ''' + @login + ''')
	BEGIN
		print ''Login already in use. Dropping existing login.''
		DROP LOGIN [' + @login + ']
	END;

	print ''Creating login''
	CREATE LOGIN [' + @login +'] WITH PASSWORD='''+ @password + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
	print ''Login created successfully''

	USE [' + @database +'];
	IF EXISTS
		(SELECT name
		 FROM sys.database_principals
		 WHERE name = ''' + @user + ''')
	BEGIN
		print ''Username already in use. Dropping existing username.''
		DROP USER [' + @user + '];
	END

	CREATE USER [' + @user + '] FOR LOGIN [' + @login + '];
	print ''User created successfully''

	ALTER ROLE [db_owner] ADD MEMBER [' + @user + '];
	print ''User added to role db_owner''
';

--print @sqlString;
SET @sqlParams = '@login NVARCHAR(100), @user NVARCHAR(100), @password NVARCHAR(500), @database NVARCHAR(100)';
EXEC sp_executesql @sqlString, @sqlParams, @login, @user, @password, @database;

Happy coding…