Tuesday, 21 February 2017

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.

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.







2 comments:

  1. I am new to SQL server and still learning about its functioning and features. This post is very informative and helped me a lot in this regard.

    ReplyDelete
  2. The budget will effectively australianwritings.com review reflect the economy of the society. According to the budget, people are planning their business or job developments. So I think budget really affects the life of each and every common people in the society.

    ReplyDelete