locked
orphaned user RRS feed

  • Question

  • Hi all,

    how can i  fixed orphaned users when my datasource server not available..?

    Wednesday, May 29, 2013 8:23 AM

Answers

  • Hello,

    If the SQL logins already exists on the target server, then you can use this script: Secure Orphaned User AutoFix

    If they not exists, then you can use sp_change_users_login (Transact-SQL), but this will add the logins with empty passwords, so take care.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Fanny Liu Tuesday, June 4, 2013 10:29 AM
    • Marked as answer by Fanny Liu Wednesday, June 5, 2013 9:11 AM
    Wednesday, May 29, 2013 8:47 AM
  • 1) This is the best Solution.
    First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.
    USE YourDB
    GO
    EXEC sp_change_users_login 'Report'
    GO

    Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.
    USE YourDB
    GO
    EXEC sp_change_users_login 'Auto_Fix''ColdFusion', NULL, 'cf'
    GO

    Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified
    USE YourDB
    GO
    EXEC sp_change_users_login 'update_one''ColdFusion''ColdFusion'
    GO

    2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.
    USE YourDB
    GO
    EXEC sp_dropuser 'ColdFusion'
    GO
    • Proposed as answer by Shashikant Shakya Wednesday, May 29, 2013 10:35 AM
    • Marked as answer by Fanny Liu Wednesday, June 5, 2013 9:12 AM
    Wednesday, May 29, 2013 9:38 AM

All replies

  • How do you know that there are orphaned users if the datasource server is not available?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, May 29, 2013 8:24 AM
    Answerer
  • What do you mean by datasource server? Is it your primary database of log shipping/ mirroring. If this is the case, then http://msdn.microsoft.com/en-us/library/ms175475(v=sql.105).aspx http://sqlblog.com/blogs/eric_johnson/archive/2008/10/17/fixing-orphaned-users.aspx

    Srinivasan

    Wednesday, May 29, 2013 8:35 AM
  • Hi uri,

    when i migrate database source server to destination server after that my source server not available

    how can i  fixed orphaned users in destination server when my datasource server not available..?


    i do not have login information from source server.
    • Edited by SLNSH Wednesday, May 29, 2013 8:40 AM
    Wednesday, May 29, 2013 8:39 AM
  • Hello,

    If the SQL logins already exists on the target server, then you can use this script: Secure Orphaned User AutoFix

    If they not exists, then you can use sp_change_users_login (Transact-SQL), but this will add the logins with empty passwords, so take care.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Fanny Liu Tuesday, June 4, 2013 10:29 AM
    • Marked as answer by Fanny Liu Wednesday, June 5, 2013 9:11 AM
    Wednesday, May 29, 2013 8:47 AM
  • 1) This is the best Solution.
    First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.
    USE YourDB
    GO
    EXEC sp_change_users_login 'Report'
    GO

    Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.
    USE YourDB
    GO
    EXEC sp_change_users_login 'Auto_Fix''ColdFusion', NULL, 'cf'
    GO

    Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified
    USE YourDB
    GO
    EXEC sp_change_users_login 'update_one''ColdFusion''ColdFusion'
    GO

    2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.
    USE YourDB
    GO
    EXEC sp_dropuser 'ColdFusion'
    GO
    • Proposed as answer by Shashikant Shakya Wednesday, May 29, 2013 10:35 AM
    • Marked as answer by Fanny Liu Wednesday, June 5, 2013 9:12 AM
    Wednesday, May 29, 2013 9:38 AM