SQL Server Performance Forum – Threads Archive
NULL LoginName for alias
Wondering if anyone has come across a good solution for the following situation. I am often in the situation where I have to restore a database from one server to another, where it is given a different name and set of logins. When the source database has aliases, the target database will have a NULL value for the LoginName (when you run sp_helpuser). Since the same user aliased on the source database does not necessarily exist on the target server, or if it does the sid is different, I am guessing the NULL is a result of the sid from the sysusers table in the restored database not matching a sid from the master..syslogins database. For example: ServerA (source db)foo aliased to dbo ServerB (restored db)
foo exists in sysusers table but shows as NULL from sp_helpuser procedure. When this happens, I just want to remove the NULL aliases. Running sp_dropalias ‘foo’ just says that alias does not exist in the database (sp_dropuser does not work either). Any ideas on how to get rid of these? What are the repercussions of just deleting the entry from the sysusers table? Thanks for your input. — Jason
You can use ‘sp_change_users_login’ to recover the user after a restore. This will re-establish the link between the database user and the corresponding SQL login. If the login does not exist, it will create one. Please check BOL for the exact syntax.
Oh! and I forgot to mention that you can then drop the user if you want using sp_dropuser. You can delete the entry from sysusers, but as always it is not recommended.
SP_CHANGE_USERS_LOGIN should fix the issue rather than dropping and recreating the user. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
sp_change_users_login Update_One ,’problematiclogin’,’problematiclogin’
Thanks for your feedback. Unfortunately, I have not been successful with the sp_change_users_login in this situation. According to BOL, when you run this procedure with the ‘REPORT’ action it should do the following: "Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login." However, when I run this option I get no results. If I try:
exec sp_change_users_login ‘Update_One’, ‘foo’, ‘foo’ Result:
"Terminating this procedure. The User name ‘foo’ is absent or invalid." Also trying:
exec sp_change_users_login ‘auto_fix’, ‘foo’ Result:
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0. Nothing seems to work – or maybe I should say I am not sure I am running this correctly. One other thing to note is that the name field in the sysusers table is actually ‘foo’ – I thought this was normal and indicates it is an alias, but I thought I would mention it. Thanks again for any feedback. — Jason
There were some changes made to this procedure in SP3. If you are using the auto_fix, the correct syntax is
EXEC sp_change_users_login ‘Auto_Fix’, ‘foo’, ‘NULL’, ‘foo’ And if you are in the right database and the REPORT option does not show results, you don’t have any orphan users in that database.
Correction: EXEC sp_change_users_login ‘Auto_Fix’, ‘foo’, ‘NULL’, ‘foo’ I am assuming the password is ‘foo’ for the ‘foo’ user and hence the second ‘foo’ in the above syntax.
Thank you for reminding me to download the BOL update – I had forgotten to do that after my sp3 upgrade. But alas, still it has not worked for me. Is the sp_change_users_login meant to work with orphaned aliases? (if that is how to describe my issue) Since that is what I am dealing with, I think that is why this is not working. Let me try to map this out with a bit more detail. The following is just an example:
Server A has source database foo1_db.
foo1_db has a user "foo" that is aliased to dbo.
Running sp_helpuser returns the following: UserName, GroupName, LoginName, DefDBName, UserID, SID
dbo, db_owner, sa, master, 1, 0x01 LoginName, UserNameAliasedTo
foo, dbo The entry for foo in sysusers is as follows (partial view): uid, status, name, sid
5, 16, foo, 0x7B2C298C001B104C97C088DD61640D5D
Server B has destination database foo2_db (restore of foo1_db from Server A).
After the restore completes, running sp_helpuser returns the following: UserName, GroupName, LoginName, DefDBName, UserID, SID
dbo, db_owner, sa, master, 1, 0x01 LoginName, UserNameAliasedTo
NULL, dbo The entry for foo in sysusers is as follows (partial view): uid, status, name, sid
5, 16, foo, 0x7B2C298C001B104C97C088DD61640D5D
What I have discovered is that if the user "foo" did not exist on the destination server, then I can use sp_dropalias ‘foo’ and it will remove the entry from the sysusers table. However, if the user ‘foo’ did already exist on the destination server, I cannot use sp_dropalias. The only way I have been able to remove the alias is to drop the foo login from the server – then sp_dropalias ‘foo’ does work. Obviously this is an undesirable solution. I feel like there should be a simple fix and I am missing the obvious. Again, I appreciate the feedback and hope that I did a better job of explaining. Please let me know if you need more info. — Jason
Nevermind, I think I have discovered the simple answer – that would be if you create a user with the same name across multiple servers, specify the sid when you run sp_addlogin and make it the same across servers. When I did this the "foo" user restored perfectly and I was able to remove if I wanted. Hope this serves some use to someone. Thanks all. — Jason
]]>