April 30, 2009 at 1:36 pm
I've inherited many bad issues when it comes to security.
I'm in the process of cleaning up an old server before moving it to a virtual machine.
I want to verify a couple thoughts to make sure it's ok to do what I'm about to do.
Example:
Database has domain\windows user as owner.
All Objects underneath are owned by dbo.
dbo is an orphaned user.
Solution:
exec Database..sp_changedbowner 'SA'
exec Database..sp_revokedbaccess 'dbo'
I've run the first command many times, but I've never run that second one.
What should I be most concerned about?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 30, 2009 at 1:50 pm
Why the second command? Curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 30, 2009 at 1:53 pm
The second command is explicitly disallowed:
exec sp_revokedbaccess 'dbo'
Msg 15150, Level 16, State 1, Procedure sp_revokedbaccess, Line 51
Cannot drop the user 'dbo'.
But once you've remapped the database owner to SA, the old orphaned dbo is no more. So I'm not sure why you would want this.
April 30, 2009 at 1:57 pm
I got it from here : http://www.sqlservercentral.com/articles/Administration/orphan_user/853/.
After running the first part the check no longer found a reason to run the second part so that answered my question. The script just doesn't have the logic to catch that.
Thanks for the input.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 30, 2009 at 2:10 pm
Yeah, it looks like that is for standard users. He has a section in the procedure where he specifically looks at the SA / dbo relationship.
Glad you were able to get it fixed. 😉
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply