My solution is to use Powershell. In this example, I have setup database
mirroring for the AdventureWorks2012 database.
For this demo, both instances, TEST1 and TEST2, are on the same physical
server.
There are two logins on the principal server that currently
do not exist on the mirror server. One
is a SQL login, AWLogin1, and the other is a Windows Authenticated login,
TRON2\AWLogin2.
The first step of our Powershell script will need to connect
to the principal server to generate a CREATE LOGIN script for those two
logins. To generate the script, we need
to grab the login name, the SID, and the hashed password if it's a SQL
login. This is accomplished by running
the following code.
SELECT 'USE master; CREATE LOGIN
' + QUOTENAME(p.name) + ' ' +
CASE WHEN p.type in ('U','G')
THEN 'FROM WINDOWS '
ELSE ''
END
+ 'WITH ' +
CASE WHEN p.type = 'S'
THEN 'PASSWORD = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' HASHED, ' + 'SID = ' + master.sys.fn_varbintohexstr(l.sid) + ', CHECK_EXPIRATION = ' +
CASE WHEN l.is_expiration_checked >
0
THEN 'ON, '
ELSE 'OFF, '
END
+ 'CHECK_POLICY = ' +
CASE WHEN l.is_policy_checked >
0
THEN 'ON, '
ELSE 'OFF, '
END +
CASE WHEN l.credential_id > 0
THEN 'CREDENTIAL = ' + c.name + ', '
ELSE ''
END
ELSE ''
END
+ 'DEFAULT_DATABASE = ' + p.default_database_name
+
CASE WHEN LEN(p.default_language_name)
> 0
THEN ', DEFAULT_LANGUAGE = '
+ p.default_language_name
ELSE ''
END
+ ';' AS 'LoginScript'
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type IN ('S','U','G')
AND p.name NOT IN ('sa', 'NT AUTHORITY\SYSTEM')
AND p.name NOT LIKE '##%##'
AND p.name NOT LIKE 'BUILTIN\%'
AND p.name NOT LIKE 'NT SERVICE\%'
ORDER BY p.name;
In this example, you can see we have one row for each of the two logins.
The next step of the Powershell script will need to write
those two rows of data to a file on the mirror server. This is done using the System.IO.StreamWriter
class.
foreach($row in $commandList.Tables[0].Rows)
{
try
{
$output = $row["LoginScript"].ToString()
$stream.WriteLine($output)
}
catch
{
$stream.Close()
CheckForErrors
}
}
When there is a need to failover to the mirror server, the
DBA can then open this script and run it.
All logins will be created and with their original SID value and
password.
The second half of the Powershell script will use the same
procedures to script out any server role memberships or server-level
permissions these two logins may have on the principal server. This is done using the following block of
code.
-- BUILD SERVER ROLE MEMBERSHIPS
SELECT 'USE master; EXEC
sp_addsrvrolemember @loginame = '+QUOTENAME(s.name)+', @rolename = '+QUOTENAME(s2.name)+';' AS 'ServerPermission'
FROM master.sys.server_role_members
r INNER JOIN master.sys.server_principals s
ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2
ON s2.principal_id = r.role_principal_id
WHERE s2.type = 'R'
AND s.is_disabled = 0
AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM')
AND s.name NOT LIKE '##%##'
AND s.name NOT LIKE 'NT SERVICE\%'
UNION ALL
-- BUILD SERVER-LEVEL PERMISSIONS
SELECT 'USE master; '+sp.state_desc+' '+sp.permission_name+' TO '+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+';' AS 'ServerPermission'
FROM sys.server_permissions sp JOIN sys.server_principals s
ON sp.grantee_principal_id
= s.principal_id
WHERE s.type IN ('S','G','U')
AND sp.type NOT IN ('CO','COSQ')
AND s.is_disabled = 0
AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM')
AND s.name NOT LIKE '##%##'
AND s.name NOT LIKE 'NT SERVICE\%';
From the output, you can see the TRON\AWLogin2 is a member
of the BULKADMIN server role and has the VIEW SERVER STATE permission. These two rows will be written to a file in
the same file share as the previous file.
As before, once the database is failed over to the mirror server, the
DBA can run this script to apply any missing permissions.
Finally, this Powershell script can be scheduled to run from
any server; however, I choose to setup this job on the principal server. I schedule it to run once a day through SQL
Agent. Each run of the script will
overwrite the existing file, so if there are any logins or permissions that
have been added or removed, it will show up in the latest version of the files.
Using this Powershell script can make it very easy to script
out logins and permissions. While
this example was used with database mirroring, then same strategy will work for
log shipping. The entire Powershell script is below.
