Post reply

Sync login account with permission between Primary and Secondary Servers

  • January 20, 2017 at 1:00 pm

    #334259

    Hello Experts

    Can anyone please share with me if you had done with the syncing of login/permissions between Primary and Stand by servers. This will not only copy the logins and will also copy the permissions, too?

    Thanks

    Thanks.

  • January 20, 2017 at 1:09 pm

    #1924146

    This is a good place to start


    http://sqlsoldier.net/wp/sqlserver/transferring-logins-to-a-database-mirror

    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/

  • January 22, 2017 at 8:05 pm

    #1924292

    Thanks Mike...

    Thanks.

  • January 23, 2017 at 5:20 am

    #1924366

    SQL-DBA-01 - Friday, January 20, 2017 1:00 PM

    Hello Experts

    Can anyone please share with me if you had done with the syncing of login/permissions between Primary and Stand by servers. This will not only copy the logins and will also copy the permissions, too?

    Thanks

    A simple script this one, this is what I use. It captures server roles assigned as well as server level securables

    select'Servername is ' + @@SERVERNAME + CHAR(10) + CHAR(13)

    union all

    select'Server principal ''' + sp.name + ''' holds SQL Server role ''' + sp2.name + ''''

    from sys.server_principals sp

    inner join sys.server_role_members srm on sp.principal_id = srm.member_principal_id

    inner join sys.server_principals sp2 on srm.role_principal_id = sp2.principal_id

    WHERE sp.principal_id > 4

    UNION ALL

    select'Server principal ''' + sp.name + ''' is a ' +

    sp.type_desc collate Latin1_General_CI_AS_KS + ' created on ''' +

    CAST(sp.create_date AS VARCHAR(25)) + ''', last modified on ''' +

    CAST(sp.modify_date AS VARCHAR(25)) + ''', default database is [' +

    sp.default_database_name + '], with ' +

    CASE srp.state_desc

    WHEN 'GRANT' THEN 'Granted'

    WHEN 'DENY' THEN 'Denied'

    END + ' permission ' + srp.class_desc + ' -> ' + srp.permission_name

    from sys.server_principals sp

    inner join sys.server_permissions srp on sp.principal_id = srp.grantee_principal_id

    inner join sys.server_principals sp2 on srp.grantor_principal_id = sp2.principal_id

    where

    sp.principal_id > 256 AND sp.name NOT LIKE 'NT[^][AS][UE]

    [HV]%\%' AND sp.name NOT LIKE '##MS%##'

    and srp.permission_name NOT IN ('CONNECT SQL', 'CONNECT')

    [/code]

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

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

  • January 23, 2017 at 7:02 am

    #1924392

    Thanks Perry!

    Thanks.

  • January 23, 2017 at 8:29 am

    #1924407

    SQL-DBA-01 - Monday, January 23, 2017 7:02 AM

    Thanks Perry!

    you're welcome

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

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

  • January 23, 2017 at 3:46 pm

    #1924499

    If the linked server connectivity does not work between the primary and stand by servers, how to send an email (dbmail) to the team with the alert so that DBAs can check what is going on with the linkedservers? Plz suggest.

    Thanks.

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

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