Re: Reparing Orphaned SQL 2005 Logins
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Thu, 29 May 2008 13:26:35 -0400
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
"Rubens" <rubensrose@xxxxxxxxxxx> wrote in message
news:erEW2pOwIHA.516@xxxxxxxxxxxxxxxxxxxxxxx
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
"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:u7HYrfOwIHA.4492@xxxxxxxxxxxxxxxxxxxxxxx
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
"Rubens" <rubensrose@xxxxxxxxxxx> wrote in message
news:uwcvLSOwIHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
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
"Tom Dacon" <tdacon@xxxxxxxxxxxxxxxx> wrote in message
news:eStThJOwIHA.5520@xxxxxxxxxxxxxxxxxxxxxxx
This works for me:
EXEC sp_change_users_login 'auto_fix', 'the_login_id'
"Rubens" <rubensrose@xxxxxxxxxxx> wrote in message
news:OiAaGzMwIHA.5584@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Reparing Orphaned SQL 2005 Logins
- From: Rubens
- Re: Reparing Orphaned SQL 2005 Logins
- References:
- Reparing Orphaned SQL 2005 Logins
- From: Rubens
- Re: Reparing Orphaned SQL 2005 Logins
- From: Tom Dacon
- Re: Reparing Orphaned SQL 2005 Logins
- From: Rubens
- Re: Reparing Orphaned SQL 2005 Logins
- From: Russell Fields
- Re: Reparing Orphaned SQL 2005 Logins
- From: Rubens
- Reparing Orphaned SQL 2005 Logins
- Prev by Date: Re: xp_logevent and service broker queues
- Next by Date: Re: xp_logevent and service broker queues
- Previous by thread: Re: Reparing Orphaned SQL 2005 Logins
- Next by thread: Re: Reparing Orphaned SQL 2005 Logins
- Index(es):
Relevant Pages
|
