SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 cannot revoke dbaccess because user own object
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RensV
Starting Member

2 Posts

Posted - 03/20/2007 :  11:35:04  Show Profile  Reply with Quote
Hi,

first I should appologize for posting my question into this server admin forum while I'm actually a newbie in sql server administration.... but I some how keep on getting involved way to deep into the sql server for our current project. And that's because we have some problems with another application which uses the MS desktop sql server 2000.
Okay I will spare you the complete story of how the problems began, but we suspect it is one database user (called TD) blocking the database because it owns some objects in this database. We only want the sa to be the user and db_owner of that specific database.
Now I've tried removing this TD user by executing the following stored procedure (with OSQL):

EXEC sp_revokedbaccess @name_in_db='TD'


Then I get the following warning:

Msg 15183, Level 16, State 1, Server DECO, Procedure sp_MScheck_uid_owns_any
thing, Line 17
The user owns objects in the database and cannot be dropped.


with a list like this (I guess these are the actual objects owned by TD??):

projects_PrimaryKey

K
SEQUENCES

U
sequences_PrimaryKey
etc


My question: is there a method to make the sa the owner of those objects and remove the user TD??

I hope that someone can help with this!

Thanks in advance,

Regards

Rens

mcrowley
Aged Yak Warrior

771 Posts

Posted - 03/20/2007 :  11:39:26  Show Profile  Reply with Quote
I would first check to see if the offending object is even being used by renaming it, and seeing what breaks. If it is beingf used, look up sp_changeobjectowner is BOL.
Go to Top of Page

RensV
Starting Member

2 Posts

Posted - 03/21/2007 :  05:09:12  Show Profile  Reply with Quote
hello,
Thanks for your answer
I've been looking into the object-owner-thing of the database but I somehow wasn't able to discover anything useful. However by coincidence I discovered the actual problem was that this 'td' user was no longer a user of the 'model' database. After adding the user all problems were solved!

Why is it that it's made so impossibly difficult to manage sql servers and databases with all the hidden options and stored procedures (even undocumented!)
But hey everything works for now and I'm happy!

Rens
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000