 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / May 2008Reparing Orphaned SQL 2005 Logins
Can someone tell me if there is a proc out there that will repair orphaned SQL 2005 users (Windows and SQL)? I have already transferred all the logins to a new server. Ideally I'd like for it to repair this for all databases, but I am fine with repairing this on an individual database level also. I came across a blog that stated: ALTER USER - addition of WITH LOGIN clause This new syntax for ALTER USER allows remapping a user to another login, by changing the user's SID value to match the login's SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. Thank-you, Rubens The command is described here: http://msdn.microsoft.com/en-us/library/ms176060.aspx Basically, you'd have to do a cursor on: sys.database_principals And for each user, run the ALTER USER.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Can someone tell me if there is a proc out there that will repair orphaned SQL 2005 users (Windows and SQL)? I have already transferred all the logins to a new server. Ideally I'd like for it to repair this for all databases, but I am fine with repairing this on an individual database level also. I came across a blog that stated: ALTER USER - addition of WITH LOGIN clause This new syntax for ALTER USER allows remapping a user to another login, by changing the user's SID value to match the login's SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. Thank-you, Rubens Ok, thank-you Tom, I will look into doing this. I did quite a bit of Google searching before I posted this and I guess I am a little surprised. I thought there might be something out there by now that outlined how to properly transfer logins to a new server and then correct the orphaned logins when databases gets restored between SQL 2000 to SQL 2005 Servers. If someone has any resources / personal experiences / documentation on how they've done this, I would really appreciate it. Thank-you, Rubens The command is described here: http://msdn.microsoft.com/en-us/library/ms176060.aspx Basically, you'd have to do a cursor on: sys.database_principals And for each user, run the ALTER USER. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Rubens" <rubensrose@hotmail.com> wrote in message news:OiAaGzMwIHA.5584@TK2MSFTNGP02.phx.gbl... Can someone tell me if there is a proc out there that will repair orphaned SQL 2005 users (Windows and SQL)? I have already transferred all the logins to a new server. Ideally I'd like for it to repair this for all databases, but I am fine with repairing this on an individual database level also. I came across a blog that stated: ALTER USER - addition of WITH LOGIN clause This new syntax for ALTER USER allows remapping a user to another login, by changing the user's SID value to match the login's SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. Thank-you, Rubens Going from 2000 to either 2000 or 2005, I have used sp_help_revlogin to ensure I have the right login info and then I use sp_change_users_login to fix it in the DB. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Ok, thank-you Tom, I will look into doing this. I did quite a bit of Google searching before I posted this and I guess I am a little surprised. I thought there might be something out there by now that outlined how to properly transfer logins to a new server and then correct the orphaned logins when databases gets restored between SQL 2000 to SQL 2005 Servers. If someone has any resources / personal experiences / documentation on how they've done this, I would really appreciate it. Thank-you, Rubens "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23LqkMGNwIHA.4896@TK2MSFTNGP03.phx.gbl... The command is described here: http://msdn.microsoft.com/en-us/library/ms176060.aspx Basically, you'd have to do a cursor on: sys.database_principals And for each user, run the ALTER USER. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Rubens" <rubensrose@hotmail.com> wrote in message news:OiAaGzMwIHA.5584@TK2MSFTNGP02.phx.gbl... Can someone tell me if there is a proc out there that will repair orphaned SQL 2005 users (Windows and SQL)? I have already transferred all the logins to a new server. Ideally I'd like for it to repair this for all databases, but I am fine with repairing this on an individual database level also. I came across a blog that stated: ALTER USER - addition of WITH LOGIN clause This new syntax for ALTER USER allows remapping a user to another login, by changing the user's SID value to match the login's SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. Thank-you, Rubens This works for me: EXEC sp_change_users_login 'auto_fix', 'the_login_id' Can someone tell me if there is a proc out there that will repair orphaned SQL 2005 users (Windows and SQL)? I have already transferred all the logins to a new server. Ideally I'd like for it to repair this for all databases, but I am fine with repairing this on an individual database level also. I came across a blog that stated: ALTER USER - addition of WITH LOGIN clause This new syntax for ALTER USER allows remapping a user to another login, by changing the user's SID value to match the login's SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. Thank-you, Rubens Okay, thank-you guys. So from the sounds of it, there is no way of automating this process? How do you determine which logins need to be fixed when passing the LoginID to sp_change_users_login? I know it EM on SQL 2000, you could look at the database users and orphaned logins would not have a login name. The Object Explorer details page in SQL 2005 doesn't show you this. Rubens > This works for me: > [quoted text clipped - 19 lines] > Thank-you, > Rubens Rubens, Perhaps sometimes the issue is in the way the logins are moved. Here is a helpful KB on moving and preserving login information that may be useful to you. http://support.microsoft.com/kb/246133/ RLF > Okay, thank-you guys. > [quoted text clipped - 29 lines] >> Thank-you, >> Rubens Thank-you for this Russell. Actually, it is method 2 I have been using to transfer the logins (sp_help_revlogin_2000_to_2005). I think I am going to have to take a step back and go through this step by step because this just isn't working for me. I created a test login, created a db, assigned that login read permission in the SQL 2000 database. I then tried restoring that database to the destination SQL 2005 server when the login existed and also when it didn't exist. However, sp_change_users_login 'auto_fix', 'InsertLoginIDHere' didn't work for me. It did not repair my test user. Is there something I am missing? Rubens > Rubens, > [quoted text clipped - 39 lines] >>> Thank-you, >>> Rubens Rubens, I see that Tom Moreau also pointed you to sp_help_revlogin. Unfortunately, I don't have another idea for you right now since I cannot recreate the problem in the small time I devoted to it. RLF > Thank-you for this Russell. Actually, it is method 2 I have been using to > transfer the logins (sp_help_revlogin_2000_to_2005). I think I am going [quoted text clipped - 57 lines] >>>> Thank-you, >>>> Rubens No problem Russell. I am getting inconsistent results in my testing so I am not sure what is up. I have found, however, that the sp_change_users_login with the 'Update_One' parameter passed seems to work all the time. So worst case scenario we can run this manually. If anyone has written a script to automate this process, I would be very interested in using it if you don't mind passing it on. Thank-you, Rubens > Rubens, > [quoted text clipped - 67 lines] >>>>> Thank-you, >>>>> Rubens Please disregard the part about identifying the login, I see it is provided by the proc. Thank-you, Rubens > Okay, thank-you guys. > [quoted text clipped - 29 lines] >> Thank-you, >> Rubens |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.