Cannot drop user after restore in 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Cannot drop user after restore in 2005

Author

Topic

MorningZ
Starting Member

44 Posts

Posted - 2006-04-11 : 11:06:01

Arrg... I just restored a database to my new laptop and am unable to drop a user that already existing in the SQL2005 database

Error i get is:

"The database principle owns a schema in the database, and cannot be dropped"

Weird thing is, i looked in the Schema section of the database, this user i am trying to drop is *not* there anyways

any tips on where else to look for schema ownage?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-11 : 12:05:59

Trying running this query in the db:

select * from sysobjects where uid in (select uid from sysusers where name = 'YourUserName')

EDIT: Oops, you said 2005. This works for 2000.

MorningZ
Starting Member

44 Posts

Posted - 2006-04-11 : 12:41:06

Well, those queiries still work in 2005 as I just tried them

got the "uid" of the user to be "18", so I said

SELECT * FROM sysobjects WHERE uid = 18 (and for kicks, did the exact query you gave as well)

results: no rows!

now i'm even more confused, lol

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-11 : 12:46:10

Any chance that user owns a DTS package or a SQL job?

TimS
Posting Yak Master

198 Posts

Posted - 2006-04-11 : 12:58:02

Does the user own the database?

Have you tied to change db owner to SA?

exec sp_changedbowner @loginame = 'sa'

Tim S

MorningZ
Starting Member

44 Posts

Posted - 2006-04-11 : 13:04:02

No DTS packages
No jobs
owner of the DB is my windows login of VAIO\Steve

So poking around further to the above recommendation of "Who Owns the DB" (which i found the above), i continued to check out some other options, and under "Options" it has this user listed, scrolling down the list of "Explicit permissions for mzuser", i see a checkbox next to "Connect"..... unchecked that and clicked "OK" to save.... went back to the same spot (right clicked DB, chose "properties", then "Permissions", now the user is not listed...

but alas, still get the same error when i tried to drop it

TimS
Posting Yak Master

198 Posts

Posted - 2006-04-11 : 13:07:11

try the following to see what schemas the 'username' owns

SELECT name
FROM sys.schemas
where principal_id = user_id('username')

MorningZ
Starting Member

44 Posts

Posted - 2006-04-11 : 13:07:12

ok, sorry for another reply, but i just saw something else interesting

right clicking on the user and selecting "properties", scrolling down the "Schemas owned by this user", the "dbo" checkbox is checked with a red box, but it does not let me uncheck it...... i'm sure thats the root of the issue

MorningZ
Starting Member

44 Posts

Posted - 2006-04-11 : 13:08:57

quote:
Originally posted by TimS

try the following to see what schemas the 'username' owns

SELECT name
FROM sys.schemas
where principal_id = user_id('username')

and that indeed returned "db_owner"

so what is this telling me?

MorningZ
Starting Member

44 Posts

Posted - 2006-04-11 : 13:11:12

arrg.. i really should fiddle more before posting, lol

Right clicking on "dbo" under the "Schemas" section shows me that the owner is the username in question

I went to Change it using the "Search" feature, but the "sa" user doesn't show up in the Browse for Users dialog, is he known as something else in 2005?

** Additionall text **
Changed the default schema owner to "dbo", and then dropped the user.... booyeah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources