Re: Orphaned user owns objects as dbo and cannot be dropped.
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 3 Aug 2006 20:38:13 -0500
I haven't run into orphaned default constraints before. You might try DBCC
CHECKCATALOG to see if turns up anything else.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A1CCBE29-5946-4235-A8FC-C53D7058853F@xxxxxxxxxxxxxxxx
Thank you Dan and Uri,
I did run the sp_change_users_login 'Report' but the DevUser was not
listed
as an orphaned user. When I run:
select name, xtype from dbo.sysobjects where uid = USER_ID('DevUser')
I got following result:
Name xtype
current_date D
empty_string D
zero D
Those three object are default constraints. I don't how this could happen?
I
tried to use sp_changeobjectowner to change the owership on these objects,
but got the following message:
Object 'zero' does not exist or is not a valid object for this operation.
So I had to directly update the dbo.sysobjects system table to chane the
uid
to 1 for these three objects, then I dropped the DevUser successfully.
What I
did might not be a safe way to solve this problem. Do you have any other
better ways to get this problem corrected?
Thank you so much for your help.
Jack
"Dan Guzman" wrote:
Normally, the database dbo user is
associated with sa login name, but this database's dbo user is
associated
with NULL.
You can fix the database owner with:
USE MyDatabase
EXEC sp_changedbowner 'sa'
"The selected
user cannot be dropped because the user owns objects. However, I
checked
all
tables, views, and stored procedures they are all owned by the dbo not
this
user. How can I get rid of this use name in this database?
I suspect this is a different problem than the database owner. The
script
below will identify objects owned by the problem user:
SELECT name, xtype
FROM dbo.sysobjects
WHERE uid = USER_ID('DevUser')
Uri mentioned that you can use sp_change_users_login to identify problem
user/login mapping.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack" <Jack@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:856F9A25-73B3-4668-8E8F-68E3CBD70C57@xxxxxxxxxxxxxxxx
Hi,
Hope someone can give me a hint. I restored a database from the
Development
SQL server to the production server. Normally, the database dbo user is
associated with sa login name, but this database's dbo user is
associated
with NULL. The developer had dbo permission on this database in the Dev
environment. Now I am trying to drop his orphaned user name from this
database on the production server, but I got an error message: "The
selected
user cannot be dropped because the user owns objects. However, I
checked
all
tables, views, and stored procedures they are all owned by the dbo not
this
user. How can I get rid of this use name in this database?
Thank you in advance.
Jack.
.
- References:
- Prev by Date: Re: Possible back door?
- Next by Date: Re: Permission error when trying to INSERT data
- Previous by thread: Re: Orphaned user owns objects as dbo and cannot be dropped.
- Next by thread: Re: Owner granting permissions on his objetcs
- Index(es):
Relevant Pages
|
