Problems re-creating a user using sp_addlogin after it was deletec in Enterprise Manager
I created a user in asp.net using the sp_addlogin stored procedure. I was able to use this user with no problems I decided that i did not need this user anymore so using enterprise manager i deleted this user.
I then decided to recreated it using using my asp.net program and sp_addlogin but i get an error that the user already exists. If i user enterprise manager to look at users it is not there. I then tried to do a sp_dropuser and i get the following error "Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12 User 'HermansonR' does not exist in the current database.
If i try to create it i get the following error "Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57The login 'HermansonR' already exists. Syntax as follows
sp_dropuser 'HermansonR'
sp_addlogin 'HermansonR' ,'xyzzy1', 'newweb'
What is really strange i can go to enterprise manage and create this user ok. i can them do a sp_dropuser and it works. but i can never do a sp_addlogin again.
What am i doing wrong?
I then decided to recreated it using using my asp.net program and sp_addlogin but i get an error that the user already exists. If i user enterprise manager to look at users it is not there. I then tried to do a sp_dropuser and i get the following error "Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12 User 'HermansonR' does not exist in the current database.
If i try to create it i get the following error "Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 57The login 'HermansonR' already exists. Syntax as follows
sp_dropuser 'HermansonR'
sp_addlogin 'HermansonR' ,'xyzzy1', 'newweb'
What is really strange i can go to enterprise manage and create this user ok. i can them do a sp_dropuser and it works. but i can never do a sp_addlogin again.
What am i doing wrong?
ASKER
Thank you for your quick response
tried
sp_change_users_login 'auto_fix' , 'HermansonR', null, 'xyzzy1'
received the following
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.
The user is not visible in the local database users.
Is there anyway to just delete HermansonR in the master database and so i can add it again with sp_addlogin. thanks
tried
sp_change_users_login 'auto_fix' , 'HermansonR', null, 'xyzzy1'
received the following
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.
The user is not visible in the local database users.
Is there anyway to just delete HermansonR in the master database and so i can add it again with sp_addlogin. thanks
Can you check in the master db (sysxlogins) to see if HermansonR is there?
This may be a case of Enterprise Manager doing it one way and Query Analyzer doing it another (that sometimes happens). I say that because you said that you use EM to delete the user (I assume in the user database? How and where did you delete poor old HermansonR?) and then used QA to run sp_addlogin (but that would add a SID in master and the user back into the database).
That's why I assumed that H was orphaned. You will have to verify that H is in master. Then check your user db to see if H is in syslogins. It may be time to delete all references to H and then use addlogin and restore H.
There are also differences in the sps. AddLogin: creates a new login for the server
DropUser drops a user in the current database.
If you want to use sp_addlogin you should also use sp_droplogin (not sp_dropuser).
This may be a case of Enterprise Manager doing it one way and Query Analyzer doing it another (that sometimes happens). I say that because you said that you use EM to delete the user (I assume in the user database? How and where did you delete poor old HermansonR?) and then used QA to run sp_addlogin (but that would add a SID in master and the user back into the database).
That's why I assumed that H was orphaned. You will have to verify that H is in master. Then check your user db to see if H is in syslogins. It may be time to delete all references to H and then use addlogin and restore H.
There are also differences in the sps. AddLogin: creates a new login for the server
DropUser drops a user in the current database.
If you want to use sp_addlogin you should also use sp_droplogin (not sp_dropuser).
ASKER
Yes HermansonR is in sysxlogins. But he is not in the the syslogins table.
I used EM to delete the user in the users database. Then when i tried to runs sp_addlogin with QA i get the error the he already exits. I guess in master because he is not in the user database.
So how can i get H out of sysxlogins?
I used EM to delete the user in the users database. Then when i tried to runs sp_addlogin with QA i get the error the he already exits. I guess in master because he is not in the user database.
So how can i get H out of sysxlogins?
sp_configure 'allow updates',1 --> allow catalog updates
delete sysxlogins
where name = 'HermansonR'
sp_configure 'allow updates',0 --> dis-allow catalog updates
delete sysxlogins
where name = 'HermansonR'
sp_configure 'allow updates',0 --> dis-allow catalog updates
ASKER
I ran sp_configure 'allow updates',1 then
delete sysxlogins
where name = 'HermansonR'
and i get the following message
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL
How do i enambe ad Hoc Updates?
delete sysxlogins
where name = 'HermansonR'
and i get the following message
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL
How do i enambe ad Hoc Updates?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
thank you for all your help that worked
thanks again i will close and accept this solution
thanks again i will close and accept this solution
ASKER
thaanks again for your help and speedy response
Cool.
Life is good.
Life is good.
There is another table, sysusers, stored in each user database. Sysusers uses the SID to determine if the login has database access.
Even though you have the same logins in your server, the SID numbers may not match. That is the confusing part, since you can see the actual names. The SID is what links the security, not the name.
Because 'HermansonR' exists, you can use sp_change_user_login to fix the SID. This link tells you more about using the procedure.
http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx