Reparing Orphaned SQL 2005 Logins

Reparing Orphaned SQL 2005 Logins

Post by Ruben » Thu, 29 May 2008 23:11:49


This is a multi-part message in MIME format.


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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content=text/html;charset=iso-8859-1>
<META content="MSHTML 6.00.6001.18023" name=GENERATOR></HEAD>
<BODY id=MailContainerBody
style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"
bgColor=#ffffff leftMargin=0 topMargin=0 CanvasTabStop="true"
name="Compose message area">
<DIV><FONT face=Arial size=2>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:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>ALTER USER - addition of WITH LOGIN
clause</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>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. <STRONG>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.</STRONG> 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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thank-you,</FONT></DIV>
<DIV><FONT face=Arial size=2>Rubens</FONT></DIV></BODY></HTML>
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Tom Morea » Thu, 29 May 2008 23:42:54

his is a multi-part message in MIME format.


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" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ...
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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content=text/html;charset=iso-8859-1>
<META content="MSHTML 6.00.2800.1609" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY id=MailContainerBody
style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"
bgColor=#ffffff leftMargin=0 topMargin=0 name="Compose message area"
CanvasTabStop="true">
<DIV><FONT face=Consolas>The command is described here:</FONT></DIV>
<DIV><FONT face=Consolas></FONT> </DIV>
<DIV><FONT face=Consolas><A
href="http://msdn.microsoft.com/en-us/library/ms176060.aspx">http://msdn.microsoft.com/en-us/library/ms176060.aspx</A></FONT></DIV>
<DIV><FONT face=Consolas></FONT> </DIV>
<DIV><FONT face=Consolas>Basically, you'd have to do a cursor on:</FONT></DIV>
<DIV><FONT face=Consolas></FONT> </DIV>
<DIV>sys.database_principals</DIV>
<DIV> </DIV>
<DIV><FONT face=Consolas>And for each user, run the ALTER USER.</FONT></DIV>
<DIV><BR>-- <BR>   Tom</DIV>
<DIV> </DIV>
<DIV>----------------------------------------------------<BR>Thomas A. Moreau,
BSc, PhD, MCSE, MCDBA, MCITP, MCTS<BR>SQL Server MVP<BR>Toronto, ON  
Canada<BR><A
href="https://mvp.support.microsoft.com/profile/Tom.Moreau">https://mvp..support.microsoft.com/profile/Tom.Moreau</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Rubens" <<A
href="mailto: XXXX@XXXXX.COM "> XXXX@XXXXX.COM </A>> wrote in
message <A
href="news: XXXX@XXXXX.COM ">news: XXXX@XXXXX.COM </A>...</DIV>
<DIV><FONT face=Arial size=
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Ruben » Fri, 30 May 2008 00:53:36

his is a multi-part message in MIME format.


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" < XXXX@XXXXX.COM > wrote in message news:% XXXX@XXXXX.COM ...
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" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ...
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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content=text/html;charset=iso-8859-1>
<META content="MSHTML 6.00.6001.18023" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY id=MailContainerBody
style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"
bgColor=#ffffff leftMargin=0 topMargin=0 CanvasTabStop="true"
name="Compose message area">
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>If someone has any resources / personal experiences
/ documentation on how they've done this, I would really appreciate
it.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thank-you,</FONT></DIV>
<DIV><FONT face=Aria
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Tom Morea » Fri, 30 May 2008 01:15:13

his is a multi-part message in MIME format.


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.

--
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" < XXXX@XXXXX.COM > wrote in message news:ulEi% XXXX@XXXXX.COM ...
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" < XXXX@XXXXX.COM > wrote in message news:% XXXX@XXXXX.COM ...
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" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ...
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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content=text/html;charset=iso-8859-1>
<META content="MSHTML 6.00.2800.1609" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY id=MailContainerBody
style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"
bgColor=#ffffff leftMargin=0 topMargin=0 name="Compose message area"
CanvasTabStop="true">
<DIV><FONT face=Consolas>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.</FONT></DIV>
<DIV><BR>-- <BR>   Tom</DIV>
<DIV> </DIV>
<DIV>----------------------------------------------------<BR>Thomas A. Moreau,
BSc, PhD, MCS
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Tom Daco » Fri, 30 May 2008 01:46:28

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
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Ruben » Fri, 30 May 2008 02:01:58

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
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Ruben » Fri, 30 May 2008 02:37:49

Please disregard the part about identifying the login, I see it is provided
by the proc.

Thank-you,
Rubens
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Ruben » Fri, 30 May 2008 02:43:17

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
 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Russell Fi » Sat, 31 May 2008 02:26:35

ubens,

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Reparing Orphaned SQL 2005 Logins

Post by Ruben » Sun, 01 Jun 2008 00:49:07

o 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

"Russell Fields" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...