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…