Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server login assigned to "dbo" user within database


Server login assigned to "dbo" user within database

Author
Message
Matt Klein-228552
Matt Klein-228552
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 565
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
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13323
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
Matt Klein-228552
Matt Klein-228552
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 565
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
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13323
Run sp_helpdb dbName.
What do you get in the "owner" column?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Matt Klein-228552
Matt Klein-228552
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 565
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.
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13323
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
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42736 Visits: 18877
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
My Blog: www.voiceofthedba.com
Matt Klein-228552
Matt Klein-228552
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 565
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.
Matt Klein-228552
Matt Klein-228552
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 565
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.
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13323
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search