Post reply

Looking for a way to reconcile logins between two SQL Server instances

  • October 16, 2015 at 4:10 pm

    #312335

    I am in the process of writing a generic PowerShell script (that executes T-SQL statements) to copy all or some databases from one SQL Server 2012 SP2 instance to a second instance that runs SQL Server 2012 SP2 or higher. The script is designed to be used on demand, and part of its execution will involve reconciling the logins between the source and destination instances.

    So...

    I am looking for a reliable way to reconcile the logins between the instances in a somewhat repetitive (i.e., "on demand") fashion. There is no SSIS in the environment; so the SSIS transfer logins task to not available to me. I've looked at sp_help_revlogin (from https://support.microsoft.com/en-us/kb/918992), but it appears to be designed for one-time use only.

    Does anyone have any ideas?

    Thanks in advance.

    J

  • October 18, 2015 at 11:03 am

    #1833968

    Have you explored the SMO functionality which may be used in powershell

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 18, 2015 at 12:44 pm

    #1833972

    No, I haven't. Can you point me to a link?

    Thanks,

    J

  • October 19, 2015 at 3:43 am

    #1834008

    shew (10/18/2015)

    No, I haven't. Can you point me to a link?

    Thanks,

    J

    Google?

    here's one link

    https://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.copyalllogins(v=sql.105).aspx

    Here's one using SMO and PowerShell too

    http://www.sqlservergeeks.com/sql-server-using-powershell-to-move-logins-between-instances/[/url]

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 19, 2015 at 8:44 am

    #1834096

    Thanks for the links. You obviously knew what you were looking for. Actually, I googled quite a bit before posting on this forum, but I didn't see anything that was specific to my needs. SMO is new territory for me. Up until this point, sqlcmd.exe has given us all the SQL Server functionality that we need from PowerShell.

    Thanks again.

  • October 19, 2015 at 9:19 am

    #1834110

    shew (10/19/2015)

    Thanks for the links. You obviously knew what you were looking for. Actually, I googled quite a bit before posting on this forum, but I didn't see anything that was specific to my needs. SMO is new territory for me. Up until this point, sqlcmd.exe has given us all the SQL Server functionality that we need from PowerShell.

    Thanks again.

    OK, no problem

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply