Another How-To post and this time focusing on scripting permissions. Here is how to script User(s), Object(s), Role(s), Server Role(s) and Server Object(s) Permissions. Beginning with the simple way via SSMS, before showing a custom script solution that I have written.
Scripting SQL Server Security: Simple Solution
In order to script the security settings for an instance of SQL server via SSMS please follow:
Right Click on your Database, Tasks, Generate Scripts, Next,
“Select specific database objects” :-> ticking users and database roles,Next
,Advanced :-> change Script Logins to true, and Script Object Level Permissions to true. (Also you can change “Script for Server Version” if appropriate”)
,OK,Next,Next
However in the script created all SQL authentication accounts will not have been produced as expected and instead this message will be present:
/* For security reasons the login is created disabled and with a random password. */
Although this is not a massive problem as the passwords can be reset from a password store and the accounts enabled again.
Scripting SQL Server Security: Custom
This script was written initially to allow permissions to be scripted out with a single click, however I have written a small custom section to also retrieve SID and password hash values so SQL Authenticated accounts are scripted as I need them.
Finally I had to change it very slightly to make it work within a SQL Job which is probably why the code is a little sloppy in places (I will refactor this!), however it is now possible to run this on a schedule to a file. See Setting up a Job.
Note for SQL 2000
The scripter allows the specification of TargetServerVersion to script for, however because the security model is substantially different between SQL 2000 and the later versions there is no guarantee that the script will work 100%. (I also do not have access to SQL 2000 to test, sorry.)
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
In a scenario with SQL 2000 it would be best to follow this article from Microsoft:
How to transfer logins and passwords between instances of SQL Server
Finally please tell me if you spot a bug, as this is very much a first draft of this script.
#http://jongurgul.com/blog/sql-server-instance-security-scripting-permissions/
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$SQLInstanceName = $Env:COMPUTERNAME; #"ServerName\InstanceName"
$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLInstanceName;
$DatabaseNames = $null #@("master","etc") comma seperated List of databases or $null for all
$BatchSeperator = $null #$null for none, or "`r`nGO" for carriage return, line feed and batch seperator.
#Options
[Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
$ScriptingOptions.AllowSystemObjects = $false
$ScriptingOptions.IncludeDatabaseRoleMemberships = $true
$ScriptingOptions.ContinueScriptingOnError = $false; #ignore scripts errors, advisable to set to $false
#Server Permissions#
"USE [master]$BatchSeperator";
#Server Logins - Integrated Windows Authentication
$SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($ScriptingOptions)} |% {$_.ToString()+$BatchSeperator};
#Server Logins - SQL Authentication
$SQLAuthLoginsCommand =
"
SELECT
'CREATE LOGIN ' + QUOTENAME(sp.[name]) + ' WITH PASSWORD = ' + master.dbo.[fn_varbintohexstr](CAST(LOGINPROPERTY(sp.[name], 'passwordhash') AS VARBINARY(256))) + ' HASHED'
+',SID = ' + master.dbo.[fn_varbintohexstr](sl.[sid])
+',DEFAULT_DATABASE = ' + QUOTENAME(sl.[default_database_name])
+',DEFAULT_LANGUAGE = ' + QUOTENAME(sl.[default_language_name])
+',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
+';'
FROM sys.sql_logins AS sl
INNER JOIN sys.server_principals AS sp ON sl.[principal_id] = sp.[principal_id]
WHERE sp.[name] <> 'sa' AND sp.[name] NOT LIKE '##%'
"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("server=$SQLInstanceName;Integrated Security=sspi;");
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.Connection = $SqlConnection;
$SqlCommand.CommandText = $SQLAuthLoginsCommand;
$SqlConnection.Open();
$Reader = $SqlCommand.ExecuteReader();
While ($Reader.Read()){$Reader[0]+$BatchSeperator;}
$SqlConnection.Close();
#Server Roles
foreach ($Role in $SQLInstance.Roles){$Role.EnumServerRoleMembers() | Where-Object {$_ -ne "sa"} |% {"EXEC master..sp_addsrvrolemember @loginame = N'{0}', @rolename = N'{1}'{2}" -f ($_,$Role.Name,$BatchSeperator);}};
#Server Permissions
$SQLInstance.EnumServerPermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee -and $_.Grantee -notlike "##*"} |% {
if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
"{0} {1} TO [{2}] {3}{4}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$_.Grantee,$wg,$BatchSeperator);
};
#Server Object Permissions
$SQLInstance.EnumObjectPermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee} |% {
if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
"{0} {1} ON {2}::[{3}] TO [{4}] {5}{6}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$_.ObjectClass.ToString().ToUpper(),$_.ObjectName,$_.Grantee,$wg,$BatchSeperator);
};
#Database Permissions#
$SQLInstance.Databases | Where-Object {$DatabaseNames -contains $_.Name -or $DatabaseNames -eq $null} |% {
$ScriptingOptions.IncludeDatabaseContext = $false;"USE ["+$_.Name+"]$BatchSeperator";#setting database context once.
#Fixed Roles
$_.Roles | Where {$_.IsFixedRole -eq $false} |% {$_.Script($ScriptingOptions)} | Sort-Object $_.ToString() |% {"{0}{1}" -f ($_.ToString(),$BatchSeperator)};#Dependency Issue. Create Role, before add to role. Solved by sort for now.
#Database Create User(s) and add to Role(s)
$_.Users | Where-Object {$_.IsSystemObject -eq $false -and $_.Name -notlike "##*"} |% {
$_.Script($ScriptingOptions)} |% {
if ($_.Contains("sp_addrolemember")){$me = "EXEC "} else {$me = ""};
"{0}{1}{2}" -f ($me,$_,$BatchSeperator);
};
#Database Permissions
$_.EnumDatabasePermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee -and $_.Grantee -notlike "##*"} |% {
if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
"{0} {1} TO [{2}] {3}{4}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$_.Grantee,$wg,$BatchSeperator);
};
#Database Object Permissions
$_.EnumObjectPermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee -and $_.Grantee -notlike "##*"} |% {
if ($_.ObjectClass -eq "Schema"){$obj = "SCHEMA::["+$_.ObjectName+"]"}
elseif ($_.ObjectClass -eq "User"){$obj = "USER::["+$_.ObjectName+"]"}
else {$obj = "["+$_.ObjectSchema+"].["+$_.ObjectName+"]"};
if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
"{0} {1} ON {2} TO [{3}] {4}{5}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$obj,$_.Grantee,$wg,$BatchSeperator);
};
};
Setting up a Job
1. Expand SQL Server Agent
2. Right Click Jobs, New Job, Type in a Name for the Job e.g. SecurityScripter
3. Change to Steps, and Add a New step which has a type “Powershell” then copy and paste the powershell code from above.
4. On the Advanced Tab Input a file directory and file name for Powershell: Outputfile e.g C:\MyServerSecurityScripter.sql or more appropriately a secured network share.
Finally run the job to make sure it works, and if all has been successful then there should be a fully scripted output.
Leave a Reply