I have a SQL 2000 database from another system. The database has user
IIS_WPG.
I restore a backup of this to a SQL 2005 database (working on a migration to
SQL 2005). Since IIS_WPG is not a Security Login on SQL 2005, when I go to
delete the database user IIS_WPG, it fails with "Drop failed for user
IIS_WPG".
How can I delete this user from the database instance?
Thanks. AmilThe fact that the DB came from SQL 2000 originally ought to be
irrelevant. As soon as you restore it on the SQL 2005 server it becomes
a SQL 2005 database (albeit in 8.0 compatibility mode but that's also
not important). You should be able to drop any users of the DB (except
dbo, which is a special user) with a simple DROP USER
<http://msdn2.microsoft.com/en-us/library/ms189438.aspx> statement in
T-SQL. For example:
use MyDB;
drop user IIS_WPG;
If this doesn't work can you please post the exactly error message that
SQL Server raises. Also posting the result of:
use MyDB;
select current_user;
select
principal_id,
cast(name as varchar(50)) as name,
type_desc
from sys.database_principals
where is_fixed_role = 0;
might help troubleshooting.
*mike hodgson*
http://sqlnerd.blogspot.com
Amil Hanish wrote:
>I have a SQL 2000 database from another system. The database has user
>IIS_WPG.
>I restore a backup of this to a SQL 2005 database (working on a migration t
o
>SQL 2005). Since IIS_WPG is not a Security Login on SQL 2005, when I go to
>delete the database user IIS_WPG, it fails with "Drop failed for user
>IIS_WPG".
>How can I delete this user from the database instance?
>Thanks. Amil
>
>|||Mike, that didn't work.
The iis_wpg user doesn't appear in the Security | Logins so I don't know why
it's complaining. Here is the query info.
use mydb;
drop user iis_wpg
Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
=======================
use mydb;
select current_user;
select
principal_id,
cast(name as varchar(50)) as name,
type_desc
from sys.database_principals
where is_fixed_role = 0;
0 public DATABASE_ROLE
1 dbo WINDOWS_USER
2 guest SQL_USER
3 INFORMATION_SCHEMA SQL_USER
4 sys SQL_USER
5 ASPNET WINDOWS_USER
6 IIS_WPG WINDOWS_GROUP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:ufZs8F%23cGHA.3888@.TK2MSFTNGP02.phx.gbl...
> The fact that the DB came from SQL 2000 originally ought to be
> irrelevant. As soon as you restore it on the SQL 2005 server it becomes
> a SQL 2005 database (albeit in 8.0 compatibility mode but that's also
> not important). You should be able to drop any users of the DB (except
> dbo, which is a special user) with a simple DROP USER
> <http://msdn2.microsoft.com/en-us/library/ms189438.aspx> statement in
> T-SQL. For example:
> use MyDB;
> drop user IIS_WPG;
> If this doesn't work can you please post the exactly error message that
> SQL Server raises. Also posting the result of:
> use MyDB;
> select current_user;
> select
> principal_id,
> cast(name as varchar(50)) as name,
> type_desc
> from sys.database_principals
> where is_fixed_role = 0;
> might help troubleshooting.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Amil Hanish wrote:
>
>|||Ah. Now we're getting somewhere. The IIS_WPG user owns a schema! OK,
you have 2 options: 1) transfer the ownership of the schema(s) owned by
IIS_WPG to a different user in the database or 2) drop the schema(s)
owned by IIS_WPG.
First you need to know which schema(s) IIS_WPG owns (obviously this
should all be done in the context of the database in question):
use myDB;
select s.* from sys.schemas as s
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'IIS_WPG';
Next you should check to see if there are any objects within that/those
schema(s):
select s.[name] as schemaname, o.[name] as objectname, o.type_desc
from sys.objects as o
inner join sys.schemas as s on s.schema_id = o.schema_id
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'IIS_WPG'
order by s.[name], o.type_desc, o.[name];
If there any no objects in the schema then you may as well just drop the
schema and be done with it:
drop schema foo;
where foo is the name of the schema. (Do this for each schema you want
to drop.) However, if the schema contains objects (views, tables,
procs, functions, etc.) that you wish to keep then you should either
transfer them to another schema (so you can drop their original schema,
which is owned by IIS_WPG) with the ALTER SCHEMA statement or transfer
ownership of the schema(s) that IIS_WPG currently owns. Transferring
ownership is easier and less likely to break code referencing those
objects. You transfer ownership with the ALTER AUTHORIZATION statement:
alter authorization on schema::foo to MyOtherDBUser;
where foo is the name of the schema you're transferring ownership of and
MyOtherDBUser is another user in the DB to which you wish to transfer
ownership (the dbo user is always a potential candidate). You'd do this
for each schema owned by IIS_WPG.
After all that the IIS_WPG user shouldn't own any schemas any more and
you should be able to drop the user with a DROP USER statement:
drop user IIS_WPG;
This is all documented in BOL if you need any more info about any of
these individual statements (including the sys.objects, sys.schemas &
sys.database_principals catalog views).
Hope this helps.
*mike hodgson*
http://sqlnerd.blogspot.com
Amil Hanish wrote:
>Mike, that didn't work.
>The iis_wpg user doesn't appear in the Security | Logins so I don't know wh
y
>it's complaining. Here is the query info.
>use mydb;
>drop user iis_wpg
>Msg 15138, Level 16, State 1, Line 2
>The database principal owns a schema in the database, and cannot be dropped
.
>=======================
>use mydb;
> select current_user;
> select
> principal_id,
> cast(name as varchar(50)) as name,
> type_desc
> from sys.database_principals
> where is_fixed_role = 0;
>0 public DATABASE_ROLE
>1 dbo WINDOWS_USER
>2 guest SQL_USER
>3 INFORMATION_SCHEMA SQL_USER
>4 sys SQL_USER
>5 ASPNET WINDOWS_USER
>6 IIS_WPG WINDOWS_GROUP
>
>"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
>news:ufZs8F%23cGHA.3888@.TK2MSFTNGP02.phx.gbl...
>
>
>|||Mike...much thanks! All your info worked like a charm and without issues.
Since the schema had no object, I was able to simply delete. I'll save your
help in case I need it in the future.
Amil
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:%233VIaFIdGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Ah. Now we're getting somewhere. The IIS_WPG user owns a schema! OK,
> you have 2 options: 1) transfer the ownership of the schema(s) owned by
> IIS_WPG to a different user in the database or 2) drop the schema(s)
> owned by IIS_WPG.
> First you need to know which schema(s) IIS_WPG owns (obviously this
> should all be done in the context of the database in question):
> use myDB;
> select s.* from sys.schemas as s
> inner join sys.database_principals as dp on dp.principal_id =
> s.principal_id
> where dp.[name] = 'IIS_WPG';
> Next you should check to see if there are any objects within that/those
> schema(s):
> select s.[name] as schemaname, o.[name] as objectname, o.type_d
esc
> from sys.objects as o
> inner join sys.schemas as s on s.schema_id = o.schema_id
> inner join sys.database_principals as dp on dp.principal_id =
> s.principal_id
> where dp.[name] = 'IIS_WPG'
> order by s.[name], o.type_desc, o.[name];
> If there any no objects in the schema then you may as well just drop the
> schema and be done with it:
> drop schema foo;
> where foo is the name of the schema. (Do this for each schema you want
> to drop.) However, if the schema contains objects (views, tables,
> procs, functions, etc.) that you wish to keep then you should either
> transfer them to another schema (so you can drop their original schema,
> which is owned by IIS_WPG) with the ALTER SCHEMA statement or transfer
> ownership of the schema(s) that IIS_WPG currently owns. Transferring
> ownership is easier and less likely to break code referencing those
> objects. You transfer ownership with the ALTER AUTHORIZATION statement:
> alter authorization on schema::foo to MyOtherDBUser;
> where foo is the name of the schema you're transferring ownership of and
> MyOtherDBUser is another user in the DB to which you wish to transfer
> ownership (the dbo user is always a potential candidate). You'd do this
> for each schema owned by IIS_WPG.
> After all that the IIS_WPG user shouldn't own any schemas any more and
> you should be able to drop the user with a DROP USER statement:
> drop user IIS_WPG;
> This is all documented in BOL if you need any more info about any of
> these individual statements (including the sys.objects, sys.schemas &
> sys.database_principals catalog views).
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Amil Hanish wrote:
>
>
