Toad World Blog

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

Feb 21, 2017 8:29:00 AM by Vinoth Nm

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.

Tags: SQL Server

Vinoth Nm

Written by Vinoth Nm

Vinoth is working as a Senior SQL Server DBA in London and has 10 years of experience as SQL Server DBA. Started his career as a DBA in SQL 6.5/7, he has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.