Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Move Logins via SMO Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 9:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 56, Visits: 265
It's possible to create in Powershell, a script that move the login from a source server to a destination server, including the password ?
I tried to use the sp_help_revlogin procedure, but I'm unable to get the t-sql output into a variable.

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $LoginDes | Write-Host $event.Message };
$smoSrc.connectionContext.add_InfoMessage($handler);
$smoSrc.connectionContext.executeNonQuery("EXEC master..sp_help_revlogin $dbLogin")

If a try to use the script() method, the code generated has the password field, but is not the sid value.


Tks
Post #1562367
Posted Thursday, April 17, 2014 5:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 4,324, Visits: 10,589
Reading the output stream from PoSh is a pain in the rear.
I would script the logins using TSQL instead:

SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' ' +
CASE type
WHEN 'S' THEN
'WITH PASSWORD = ' + CAST(CONVERT(varchar(256), CAST(LOGINPROPERTY(name,'PasswordHash') AS varbinary (256)), 1) AS nvarchar(max)) + ' HASHED ,
SID = ' + CAST(CONVERT(varchar(256), Sid, 1) AS nvarchar(max))
ELSE ' FROM WINDOWS '
END , type
FROM sys.server_principals
WHERE type IN ('S','U','G')

It may not be perfect, but it's a starting point.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1562611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse