Quick Tips: Add SQL User/Rolemember to all Databases in SQL Server

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.

--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.