DB USER LOGON NAME CHANGE

[OdedShafran]

Hi all!

I am a very unfamiliar with SQL, so with your answer, please be as specific as possible so you can help me solve my issue.

I have 2 DBS in my SQL 2000, and when i go to section USERS in everyone, I have a user OBS_COMMON without a login... (Login is empty).
this is causing some little issues i want to resolve.
the problem is, that I cant change its logon name to something I want! theres no such options available.
and If i want to delete it, it says i cant, becuase its DB OWNER\CREATOR... (so i went to its properties and i cleared the V mark in the PB_OWNER)
still, wont let me change its loginname (i have another user there, so OBS_COMMON is not the only one there anyway!)

please, if you know how can i change its login, please tell me how to do it (again, im a beginner in SQL)...


Thanks All!!

Oded Shafran, XP MCP

Oded Shafran,
Network Administrator

 

[SQLBill]

1st-do you have System Admin or Security Admin privilege on the databases? If not, then you can't change anything.

if you do,
2nd-you are probably going to USERS under the database. That's the wrong place. Go to SECURITY, this is under the server not the individual databases. Expand that (click on the +). Click once on LOGINS. This is where you change, add, or remove LOGINS.

3rd-You cannot change LOGIN names. You have to create a new LOGIN if you want a different name.

4th-Once you have created a LOGIN, you have to apply permissions. Go to Database Access tab and apply permissions in that area. First you click the box next to the database, that puts a green check meaning they can access it. Then go to the bottom window and give them the type of access they need.

You must ALWAYS have at least one login that is DBO (database owner).

So, go to Security, then Logins, find the OTHER login and right click on it. Select Properties. Go to Database Access tab and in the bottom window, put a check mark in the box for db_owner. Click ok. Go to the login OBS_COMMON. Right click on it and select DELETE. Right click on LOGINS, select NEW LOGIN. Fill out the information (Name, Authentication, Password, Default database, default language) then go to the database access tab and fill out the information there (give access to the database and assign the proper access).

-SQLBill

 

[OdedShafran]

Ok listen.

I have other DBS that OBSCOMMON user name has a login of OBSCOMMON, but with the problematic DBS, OBSCOMMON user, doesnt have any LOGIN NAME.
I think the problem was due to a restore of these DBS...
anyway, I went where you said about Security, I chose OBSCOMMON, and the probs DBS didnt have mark of OBS COMMON!
so I thought "YES!!! I CAN FIX IT NOW", but wrong, when i mark them, as well as with DB_OWNER, it says those DBS already have OBSCOMMON user name there! (but it wont be marked within the sec tab!

I went again to a prob DB, and tried again to delete the wrong OBSCOMMON username, and the correct error it says that it cannot delete OBSCOMMON, becuase it own objects!

so, If i cant remove or delete it, how can I still change its logon name?!

thanks!

Oded Shafran,
Network Administrator

 

[SQLBill]

Okay, you gave me more information. What you have is known as an ORPHANED USER. And yes this can happen when a Database is restored.

Change the owner of the database to SA (go to Security, Logins, right click on SA, select Properties. Go to Database access tab and highlight the database. Go to the bottom window and make sure db_owner is checked.)

Open Query Analyzer.
Run this query (make sure you change "database_name" to the name of your problem database):

USE database_name
GO
EXEC sp_revokelogin 'OBSCOMMON'
GO

This should remove the orphaned user from the database. Then go back to Security and Logins. Right click on OBSCOMMON, select properties and give it the proper permissions. Or delete it and create a new one.

-SQLBill

 

[OdedShafran]

it says:
Server: Msg 15407, Level 11, State 1, Procedure sp_revokelogin, Line 31
'OBSCOMMON' is not a valid Windows NT name. Give the complete name: <domain\username>.

Oded Shafran,
Network Administrator

 

[OdedShafran]

If i will delete the OBSCOMMON from the DB, wont it loose the objects it owns!?
I am afraid to delete it...


Oded Shafran,
Network Administrator

 

[SQLBill]

'OBSCOMMON' is not a valid Windows NT name. Give the complete name: <domain\username>.

Okay, I went by the name you provided. What is the actual name shown? Is it a two part name separated by a \ like this: mydomain\OSBCOMMON ?

If so run the same command but change it to 'domain_name\OBSCOMMON' Also make sure the name is spelled the way it is in the database. You've spelled it two ways in your post - OBS_COMMON and OBSCOMMON.

If i will delete the OBSCOMMON from the DB, wont it loose the objects it owns!?
I am afraid to delete it...

What objects does it own?

It's only Revoking the login to the database, it's not deleting it. First you have to revoke the login (by running the command I provided)and then you add the login back in (by following the instructions for Security>Logins that I provided).

You can always create a temporary login and assign the objects to that login. Then follow the directions and reassign the objects then delete the temporary login.

If you are really nervous, backup the database first and then do what I suggested. If something goes wrong, just restore the database. In a case like this, it's hard to give perfect advice. I can't see your database and may not know the whole situation. You might not be telling me something I need to know because you don't know that I need the information.

I suggest going to the Books OnLine and looking up Orphaned Users.

-SQLBill

 

[OdedShafran]

i gave the domain\obscommon ,but it says the user cant be found...
in the SQL manager it is displayed OBSCommon...


Oded Shafran,
Network Administrator

 

[LonnieJohnson]

Hello Gang,

I have a user that has a name change. I have a front end interface that sets up the user on SQL Server. I would like to be able to delete a user programmatically. I see how to use a sp to add or grant access for a user but not how to delete one.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[SQLBill]

Lonnie's post is a cross-post. The other post already has a response. Please direct any comments, etc concerning Lonnie's question to THREAD183-670288.

-SQLBill

 

[TonyKhela]

Hi Oded Shafran,

After a restore or dettach/attachdb a user in a DB can lose its mapping to the login. (Because the login does not exist in the new server).

Heres the solution:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;305711

CAUSE
Enterprise Manager is incorrectly filtering out all the users that do not have matching logins, and the dbo user does not have a matching login. Two possible reasons for this behavior to occur are:
If a database is created by a Microsoft Windows NT authenticated login that is granted access to the computer that is running SQL Server through group membership (such as BUILTIN\Administrators), the security identification number (SID) stored in the sysusers system table in that database does not have a matching SID in the syslogins system table.


If a database is restored where the dbo user's SID in the sysusers system table is not matched with the SID in the syslogins system table.
WORKAROUND
To work around this behavior, use either of these methods:
If the dbo user does not have an explicit login, change the owner of the database to a user that has an explicit login. For example, change the owner of the database to the system administrator (sa), and then execute the following code:exec sp_changedbowner 'sa'

If the dbo user does have an explicit login but that user's SID in the sysusers system table does not match the login's SID in the syslogins system table (possibly due to a database restore) use the sp_change_users_login stored procedure to restore the SID mapping.