August 10, 2009 at 2:16 pm
I have 3rd party software which has a server login assigned to "dbo" user within several of the databases. The users cannot see any of the databases where this is occurring. I cannot change what the login points to within the database as I cannot drop "dbo". I also cannot add it as a user as the user already exists in the database .... as dbo.
It seems that there should be an easy solution but I am unable to find one after digging around on the 'net. Any thoughts?
Thanks!
Matt
August 11, 2009 at 6:41 am
Check who's the database owner.
If it is your 3rd party sw user, run sp_changedbowner to assign to another user.
Hope this helps
Gianluca
-- Gianluca Sartori
August 11, 2009 at 7:15 am
Thanks for your reply. That is not the issue. Let me try to explain again.
The login "qc" has rights to 16 different databases. For 13 of the databases, when I run sp_helplogins, the login "qc" has the user_name "qc" into the database. For 3 of the databases, somehow the login "qc" has the user_name "db_owner". Those 3 databases are not accessible for the users via the application. I cannot find a way to change that relationship.
If I look in the database under security, the "qc" user does not exist in the database. If I run sp_adduser 'qc", I get the Msg 15063 error stating that the login already has an account under a different name -- which is dbo. I cannot run sp_dropuser as the 'qc' user does not exist. When I go out to login properties for the 'qc' login, I cannot uncheck the box for that database as when I try to save it, I get a "cannot drop user dbo" error.
I cannot find an answer. Any suggestions are welcome.
Thanks!
-Matt
August 11, 2009 at 7:34 am
Run sp_helpdb dbName.
What do you get in the "owner" column?
-- Gianluca Sartori
August 11, 2009 at 7:40 am
Our system administrator login is the database owner. It is the same on the other databases as well. I did run the sp_helpdb to double-check, but that is the case. I am logged in as that user.
August 11, 2009 at 7:48 am
Have you tried sp_droprolemember 'db_owner', 'yourAccount'?
An alternative to this is sp_dropalias 'yourAccount', which is deprecated but should still work.
-- Gianluca Sartori
August 11, 2009 at 7:52 am
check sys.database_principals in those databases. Is QC a user in there that is perhaps orphaned from a SID? Or sp_change_users_login with the report option.
August 11, 2009 at 8:20 am
Thanks for the suggestions.
sp_droprolemember 'db_owner', 'qc' gave me the same "user or role 'qc' does not exist in this database".
sp_dropalias 'qc' gives me a "no alias exists for the specific user" message.
When I run select * from sys.database_principals, the name of the 'qc' user does not exist.
When I run sp_change_users_login 'REPORT', I get nothing.
The strange part to me is that when I am in the GUI in login properties for that 'qc' login, database after database have the user 'qc' and a couple of them have 'dbo'. I am out of ideas.
August 11, 2009 at 8:23 am
In the other databases, which the users can get into, when I run the select from sys.database_principals, the 'qc' user does appear as a SQL_USER.
August 11, 2009 at 8:58 am
Let's cut the whole thing into two parts:
1) Orphaned user 'qc'
2) Users not logging to databases
For number 1 I still suggest changing db owner. Change it to what it already is, I thing it is worth trying and for sure it won't hurt.
For number 2 it depends on the users rights, so you can go and fix it the normal way.
Does it make sense?
-- Gianluca Sartori
September 9, 2010 at 3:48 am
blurb - ignore me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply