locked
Sync users and logins RRS feed

  • Question

  • Hi Team,

     

    we have find out some of login/users 20 are not  there in QA Env. How can we sync these users and logins please suggest me

     

    before that what is sync (syncranization)?

     

    Tx

     


    subu
    Tuesday, August 2, 2011 9:54 AM

Answers

  • You will need to run on every database on every server ALTER USER uname WITH LOGIN=login

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Stephanie Lv Wednesday, August 10, 2011 6:57 AM
    Tuesday, August 2, 2011 10:46 AM
  • If I understand this correctly, this SELECT may help:

    SELECT 'ALTER USER ' + quotename(name) + ' WITH LOGIN = ' + quotename(name)
    FROM   sys.database_principals
    WHERE  type_desc = 'SQL_USER'
      AND  coalesce(suser_sname(sid), '') <> name
      AND  principal_id > 4

    Copy, paste and execute result. You need to repeat it per database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Wednesday, August 10, 2011 6:57 AM
    Tuesday, August 2, 2011 8:57 PM

All replies

  • Migrate login to another instance .. check this http://support.microsoft.com/kb/246133
    MCITP, MCTS, MCDBA,MCP
    Tuesday, August 2, 2011 10:28 AM
  • hi team,

     

    I am not Migrate the logins we have around 22 server we are follow based server only in other servers some of the users are not there now   i want sync the users and logins.

     

    can you any body plz suggest me.

     

    Tx

     


    subu
    • Marked as answer by Stephanie Lv Wednesday, August 10, 2011 6:57 AM
    • Unmarked as answer by Stephanie Lv Wednesday, August 10, 2011 6:57 AM
    Tuesday, August 2, 2011 10:38 AM
  • You will need to run on every database on every server ALTER USER uname WITH LOGIN=login

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Stephanie Lv Wednesday, August 10, 2011 6:57 AM
    Tuesday, August 2, 2011 10:46 AM
  • If I understand this correctly, this SELECT may help:

    SELECT 'ALTER USER ' + quotename(name) + ' WITH LOGIN = ' + quotename(name)
    FROM   sys.database_principals
    WHERE  type_desc = 'SQL_USER'
      AND  coalesce(suser_sname(sid), '') <> name
      AND  principal_id > 4

    Copy, paste and execute result. You need to repeat it per database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Wednesday, August 10, 2011 6:57 AM
    Tuesday, August 2, 2011 8:57 PM
  • If you are looking for mapping the users in the database with the logins in the server you can run the below script to sync all users in a database.

    DECLARE @v_User     SYSNAME
           ,@v_Sid      UNIQUEIDENTIFIER

    DECLARE c_usersec CURSOR FAST_FORWARD FOR
    SELECT a.name, a.sid
    FROM [databasename].dbo.sysusers  a (NOLOCK) -- Provide databasename to be run on
    JOIN [master].[dbo].[syslogins] b (NOLOCK)
    ON a.name collate SQL_Latin1_General_CP1_CI_AS = b.name collate SQL_Latin1_General_CP1_CI_AS
    WHERE a.name NOT IN ('public', 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter')
    AND a.Sid <> b.sid
    ORDER BY a.Name

    OPEN c_usersec

    FETCH NEXT FROM c_usersec INTO @v_User, @v_Sid

    WHILE (@@FETCH_STATUS <> -1)
        BEGIN

        EXEC [databasename].[dbo].[sp_change_users_login] 'Update_One', @v_User, @v_User -- Provide databasename to be run on

        FETCH NEXT FROM c_usersec INTO @v_User, @v_Sid

        END

        CLOSE c_usersec

        DEALLOCATE c_usersec

     

    You have to change the databasename with the actual database name at two locations where commented. You should run the above script for each database.

    Hope this helps.


    Thanks, Manu Please mark this as answered if this answers your question
    Tuesday, August 2, 2011 10:54 PM