Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Server login assigned to "dbo" user within database Expand / Collapse
Author
Message
Posted Monday, August 10, 2009 2:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:48 PM
Points: 57, Visits: 521
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
Post #768175
Posted Tuesday, August 11, 2009 6:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:58 PM
Points: 4,655, Visits: 11,126
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #768537
Posted Tuesday, August 11, 2009 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:48 PM
Points: 57, Visits: 521
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


Post #768562
Posted Tuesday, August 11, 2009 7:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:58 PM
Points: 4,655, Visits: 11,126
Run sp_helpdb dbName.
What do you get in the "owner" column?


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #768582
Posted Tuesday, August 11, 2009 7:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:48 PM
Points: 57, Visits: 521
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.
Post #768589
Posted Tuesday, August 11, 2009 7:48 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:58 PM
Points: 4,655, Visits: 11,126
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #768597
Posted Tuesday, August 11, 2009 7:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #768601
Posted Tuesday, August 11, 2009 8:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:48 PM
Points: 57, Visits: 521
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.



Post #768646
Posted Tuesday, August 11, 2009 8:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 1:48 PM
Points: 57, Visits: 521
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.
Post #768652
Posted Tuesday, August 11, 2009 8:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 2:58 PM
Points: 4,655, Visits: 11,126
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #768686
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse