April 12, 2012 at 8:26 pm
Please Help, I am a newbie!
I just upgraded a database from SQL Server 2000 to 2008 R2. The last step I was doing was resolving orphan users. I created the logins and then ran exec sp_change_users_login 'Report'. I had a few orphans so I ran the following code to sync the logins:
DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser
Go
It was running for a long time without any new users so I canceled executing the query. I ran the sp_change_users_login for the orphans that were originally reported and then ran sp_change_users_login 'Report' again and no orphans were reported.
I didn't think it would hurt to run the code again but when I executed the same code I now get:
CAI11mNT Synchronization of Logins in Progress
Msg 15289, Level 16, State 1, Procedure sp_change_users_login, Line 75
Terminating this procedure. Cannot have an open transaction when this is run.
CAI12mNT Synchronization of Logins in Progress
Msg 15289, Level 16, State 1, Procedure sp_change_users_login, Line 75
Terminating this procedure. Cannot have an open transaction when this is run.
.
.
.
for many users. I am still able to run sp_change_users_login 'Report' and I was able to run
sp_change_users_login 'Update_one', 'CAI11mNT', 'CAI11mNT' (first user in the errrors) without a problem.
What should I do now?
April 13, 2012 at 6:23 am
chumphrey 12211 (4/12/2012)
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
Firstly, use the catalog sys.database_principals and not sysusers in future
Are you creating all logins first then synchronising?
If so, why not just let the script create and synch any users it finds without a server level login?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 13, 2012 at 6:24 am
chumphrey 12211 (4/12/2012)
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
Firstly, use the catalog sys.database_principals and not sysusers in future
Are you creating all logins first then synchronising?
If so, why not just let the script create and synch any users it finds without a server level login?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 13, 2012 at 9:29 am
I was following documentation from the web. Yes, I first created the logins and I should have just let it run but it was late and I was anxious. My issue is what do I do now that I get the errors when I try to run the code again? I dont understand what the error messages are telling me.
April 13, 2012 at 11:28 am
What documentation on the web?
Here is the official documentation for sp_change_users_login and is states:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.
In other words, start using ALTER USER for new development.
Try it like this in a new query window to make sure you do not have an open, uncommitted transactions in the session. When you're ready uncomment the EXEC statement to have it do work.
DECLARE @UserName NVARCHAR(255),
@sql NVARCHAR(MAX)
DECLARE Cursor_OrphanedUser CURSOR
FOR
SELECT name
FROM sys.database_principals
WHERE type_desc = 'SQL_USER'
AND sid IS NOT NULL
AND sid <> 0x01
AND name NOT LIKE '##%##'
AND name NOT IN ('guest')
AND SUSER_SNAME(sid) IS NULL
ORDER BY name
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
SET @sql = 'ALTER USER ' + QUOTENAME(@UserName) + ' WITH NAME = ' + QUOTENAME(@UserName) + ', LOGIN = ' + QUOTENAME(@UserName)
PRINT @sql
--EXEC(@sql)
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 13, 2012 at 1:09 pm
Mea Culpa, my bad for not doing more research! I ran this new code with the "alter user" but nothing was returned. I'm guessing I had already resolved the orphans using the code with the deprecated command?
As it turns out, I am going to have to do this upgrade again. The next time, do I just need to run the "alter user" script? If I run it with the EXEC commented out first, will that tell me the orphan users that the script will fix (like sp_change_users_login 'Report' does)? THANK YOU for the help and guidance.
April 13, 2012 at 1:27 pm
chumphrey 12211 (4/13/2012)
Mea Culpa, my bad for not doing more research! I ran this new code with the "alter user" but nothing was returned. I'm guessing I had already resolved the orphans using the code with the deprecated command?
It's quite possible. Deprecated commands just mean they will be removed in a future version but will still function. A lot of times though, while deprecated commands will function on newer versions they do so in a limited capacity, i.e. they may not take functionality introduced in the newer version into account which can produce unexpected results. I am not sure if sp_change_users_login falls into this category but it's just good business to keep moving forward with the latest ways of doing things when documented. Thanks for not taking my comments as a "blast", I just try to keep people moving forward with the technology and with Google and Bing lots of people read these posts at later times so we want to call it out when we can.
As it turns out, I am going to have to do this upgrade again. The next time, do I just need to run the "alter user" script? If I run it with the EXEC commented out first, will that tell me the orphan users that the script will fix (like sp_change_users_login 'Report' does)? THANK YOU for the help and guidance.
Yes, leaving the EXEC commented will basically give you the "report". I like to run things this way in the beginning, and run the results myself until I implicitly trust what a script is doing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 13, 2012 at 1:42 pm
Thanks for the reply, I did not take your comments as a Blast at all. I need all the help I can get! 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply