Most of times when you have dedicated SQL Server for an Application
clients come up with a request to add a user to all databases in the SQL Server
with certain permission, while its east to do it via SSMS for if the system has
only couple of databases but imagine your SQL instance have hundreds of
databases then scripting is the way to go, I have assumed the login name is ‘Test’ client has request db_datareader and db_datawriter permission for our
example blog,
There are 2 ways I am going to accomplish this one using TSQL and
another using Powershell.
TSQL:-
--Add
User
EXEC sp_MSforeachdb 'USE ?;CREATE USER Test
FOR LOGIN Test'
GO
--Add
Role member
EXEC sp_MSforeachdb 'USE ?;ALTER ROLE
db_datareader ADD MEMBER Test'
GO
--Add
Role member
EXEC sp_MSforeachdb 'USE ?;ALTER ROLE
db_datawriter ADD MEMBER Test'
Powershell:-
Clear-Host
Import-Module SQLPS -DisableNameChecking -ErrorAction SilentlyContinue
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "1MKBG12"
$dbs = $srv.databases
$loginname = 'Test' ##########Provide
Login name to check#############
$chkloginflag = $null
$chkloginflag = $srv.Logins|where {$_.name -eq 'Test3'}
if($chkloginflag -eq $null -or $chkloginflag -eq '')
{
$login = New-Object ('Microsoft.SqlServer.Management.Smo.Login') ($srv, $loginname)
$login.LoginType = 'SqlLogin' #https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.logintype.aspx
$login.PasswordExpirationEnabled = $false
$Login.PasswordPolicyEnforced = $false
$login.Create("test")
forEach($db in $dbs)
{
$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $loginname)
$usr.create()
$usr.AddToRole("db_datareader")
$usr.AddToRole("db_datawriter")
}
}
The above PowerShell script also
creates a Login if it does not exist then creates the SQL user in all databases
with reader and writer roles. I have written a simple script assuming the Login
to be SQL Authentication. You can tweak the $Login object to create Windows
User/Group Login.
