Post reply

Comparison of SQL Login with SID

  • October 12, 2015 at 8:06 am

    #321814

    I want to compare SQL server Logins along with SID's across following 6 servers which includes Development, UAT, STG and Production

    Is there any query available or is there any easy way to do so?

    Please help

  • October 12, 2015 at 9:37 am

    #1832830

    if you have linked servers set up, then yes, it's pretty easy:

    SELECT * FROM

    [Development].master.sys.server_principals T1

    INNER JOIN [UAT].master.sys.server_principals T2

    ON T1.name = T2.name

    WHERE T1.sid <> T2.sid

    if you don.t then you need to copy + paste results from one server, convert it to a CTE, so that you can join it to another server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 12, 2015 at 10:26 am

    #1832836

    Setting up a central management server should work. The query can then be run against all of the registered servers

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • October 13, 2015 at 11:58 am

    #1833049

    how can I run query against all of the register server? can you please send me the query which can be run across all register servers?

    Thanks

  • October 15, 2015 at 8:31 am

    #1833522

    The steps would be to create a central management server, and register all of your servers.

    This article explains it:

    https://msdn.microsoft.com/en-us/library/bb934126.aspx?f=255&MSPPError=-2147217396

    This one will give you additional details:

    https://msdn.microsoft.com/en-us/library/bb895144.aspx

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

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

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