PowerShell : Scripting Logins, Role Assignments and Server Permissions Using PowerShell

This post is a continuation of Multiple DB refresh automation using PowerShell post . The OP requested me to automate the login transfer process as well. The script should generate an outfile and execute the file on the target server.

<#
.ProblemStatement

Copying SQL server logins to file is little bit tricky though we are able to generate the script the password need to handled in a proper way to make it work.
Usually we’ll make use of scripts like sp_help_revlogin to manage copying logins from one instance to another but the requirement won’t suffice our purpose it won’t generate the script for server permissions and role assignments.
When you are automating the entire refresh process,PowerShell provides a flexibility to run SQL and generate the scripts in the said path. The SQL file can be invoked using invoke-sqlcmd to to execute it across any target server

The below code loads the snapins

SqlServerProviderSnapin100
SqlServerCmdletSnapin100

.Example1

To generate the script

PS:\>Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut

.Example2

Generate and apply script on to the target server

PS P:\> Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut -TargetServer <ServerName>

.Reference Link

Scripting Out the Logins, Server Role Assignments, and Server Permissions

#>

 

function Copy-Logins{
    [cmdletbinding()]
    Param
    (
    [parameter(Mandatory=$true)][string] $Sourceserver,
    [String] $TargetServer,
    [string] $outpath)

##Create a new outfile at query function call

[string]$FileDS = Get-Date -Format "yyyyMMdd" 

[string]$outFile = $outpath +'\'+ $Sourceserver+'_'+$FileTS + "_login.sql" 

New-Item -ItemType file $outfile -Force

##Prepare the connection string 

$SqlConnection = New-Object System.Data.SQLClient.SQLConnection("Server=$Sourceserver;Integrated Security=sspi;");
$SqlCommand = New-Object System.Data.SQLClient.SqlCommand; 
$SqlCommand.Connection = $SqlConnection;   

Try
{

##SQL Account - Executing a SQL is better than working with Hashed value

$SQL=  "
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
			   CASE 
					WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
						+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
					ELSE ' FROM WINDOWS WITH'
				END 
	   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
		ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
		AND SP.name NOT LIKE '##%##'
		AND SP.name NOT LIKE 'NT AUTHORITY%'
		AND SP.name NOT LIKE 'NT SERVICE%'
		AND SP.name <> ('sa')"

$SQL2="SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
	JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
	JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
		AND SL.name NOT LIKE '##%##'
		AND SL.name NOT LIKE 'NT AUTHORITY%'
		AND SL.name NOT LIKE 'NT SERVICE%'
		AND SL.name <> ('sa')"

$SQL3="
SELECT 
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
		THEN SrvPerm.state_desc 
		ELSE 'GRANT' 
	END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
		THEN '' 
		ELSE ' WITH GRANT OPTION' 
	END collate database_default AS [-- server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
	JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
		AND SP.name NOT LIKE '##%##'
		AND SP.name NOT LIKE 'NT AUTHORITY%'
		AND SP.name NOT LIKE 'NT SERVICE%'
		AND SP.name <> ('sa')"
 
        $SqlCommand.CommandText = $SQL;   
        $SqlConnection.Open(); 
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
        $SqlConnection.Open(); 
        $SqlCommand.CommandText = $SQL2;   
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
        $SqlConnection.Open(); 
        $SqlCommand.CommandText = $SQL3;   
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();

}
catch
{
 $ErrorMessage = $_.Exception.Message
 Write-host "$Sourceserver does't exist or Invalid SQL instance Name $ErrorMessage"
}

if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin100'})) 
{
   add-pssnapin SqlServerProviderSnapin100 
   write-host "Loading SqlServerProviderSnapin100 in session" 
} 
else 
{ 
  write-host "SqlServerProviderSnapin100 is already loaded" 
} 

if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'})) 
{   
    add-pssnapin SqlServerCmdletSnapin100 
   write-host "Loading SqlServerCmdletSnapin100 in session" 
} 
else 
{ 
  write-host "SqlServerCmdletSnapin100 is already loaded" 
} 


 If($TargetServer){
 try
    {
    Invoke-Sqlcmd -ServerInstance $TargetServer -InputFile $outFile -ErrorAction Stop
    }
catch
    {
     Write-host "$TargetServer not reachable "
     }

 }


}   
   

Please refer the below link which gives a full list of required details

SQL Server Instance Security: Scripting Permissions

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , . Bookmark the permalink.

6 Responses to PowerShell : Scripting Logins, Role Assignments and Server Permissions Using PowerShell

  1. RK says:

    Hi Prashanth,

    I’m looking only for server roles for multiple servers.Hope u replied last time, but couldn’t find the scripts.

  2. Hi RK,

    Try this

    change the input file path f:\PowerSQL\server.txt and output directory.

    Function Script_ServerRoles
    {
    param(
    [String]$sqlserver,
    [String]$DirectoryName
    )
    #$directoryname= ‘E:\Output\’
    #$sqlserver=’AQDBSP18′
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
    $srv = New-Object (“Microsoft.SqlServer.Management.SMO.Server”) ($sqlserver)
    foreach ($Role in $srv.Roles){$Role.EnumServerRoleMembers() | Where-Object {$_ -ne “sa”} |% {“EXEC master..sp_addsrvrolemember @loginame = N'{0}’, @rolename = N'{1}'{2}” -f ($_,$Role.Name,”`r`nGO”);}|Out-File $($directoryname + $sqlserver + “_roles.sql”) -append
    };

    }
    foreach($server in get-content F:\PowerSQL\server.txt)
    {
    Script_ServerRoles -sqlserver $server -DirectoryName “f:\PowerSQL\”
    }

  3. samarjun says:

    Hi
    I’m new to PS.. seems I’m wrong in using your script somewhere.

    have saved file with name copyloginssourceServer.ps1 and ran it with following command.

    .\copyloginssourceserver.ps1 ‘servername’ -outpath D:\Output

    Not getting any error.. but file is not able to generate into location. am I doing anything wrong?

    have verified internal scripts from ssms and they returns expected result.

  4. samarjun says:

    Yes Prashanth. I do have privilege to create files.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s