Hi there,
I try to remove logins and users and finally drop a database using the following script:
use master /* database in use cannot be dropped */
go
if convert(int, convert(char(1), SERVERPROPERTY('ProductVersion'))) > 8
begin
select 'SQL Server 2005'
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE '<DOMAIN\My-User>' AND type='U') exec sp_revokelogin [<DOMAIN\My-User>]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE '<DOMAIN\My2-User>' AND type='U') exec sp_revokelogin [<DOMAIN\My2-User>]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE 'ovdb_user' AND type='S') exec sp_droplogin 'ovdb_user'
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE 'ovms_admin' AND type='S') exec sp_droplogin 'ovms_admin'
end
else
begin
select 'SQL Server 2000'
IF EXISTS (SELECT * FROM syslogins WHERE name LIKE '<DOMAIN\My-User>' AND isntname='1') exec sp_revokelogin [<DOMAIN\My-User>]
IF EXISTS (SELECT * FROM syslogins WHERE name LIKE '<DOMAIN\My2-User>' AND isntname='1') exec sp_revokelogin [<DOMAIN\My2-User>]
if (SELECT name FROM sysdatabases WHERE name = 'openview') = 'openview'
begin
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovdb_user' AND isntname='0')
begin
exec openview..sp_droprolemember 'role_ovdb_user', 'ovdb_user'
exec openview..sp_droprole 'role_ovdb_user'
end
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovdb_user' AND isntname='0') exec openview..sp_dropuser 'ovdb_user'
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovms_adm' AND isntname='1') exec openview..sp_dropuser 'ovms_adm'
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovms_admin' AND isntname='0') exec openview..sp_dropuser 'ovms_admin'
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovms_deleg' AND isntname='1') exec openview..sp_dropuser 'ovms_deleg'
use master
end
end
go
/* Drop entire database. Files will be removed. */
if (select name from sysdatabases where name = 'mydb') = 'mydb'
drop database mydb
go
Now, when I execute this script, SQL Server shows the following error message:
"The user owns objects in the database and can not be removed." The original Message is in german, this is only a translation.
Does anyone know how I can solve this and force the removal of the users?
Thanks you in advance for your help!
Chris
I try to remove logins and users and finally drop a database using the following script:
use master /* database in use cannot be dropped */
go
if convert(int, convert(char(1), SERVERPROPERTY('ProductVersion'))) > 8
begin
select 'SQL Server 2005'
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE '<DOMAIN\My-User>' AND type='U') exec sp_revokelogin [<DOMAIN\My-User>]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE '<DOMAIN\My2-User>' AND type='U') exec sp_revokelogin [<DOMAIN\My2-User>]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE 'ovdb_user' AND type='S') exec sp_droplogin 'ovdb_user'
IF EXISTS (SELECT * FROM sys.server_principals WHERE name LIKE 'ovms_admin' AND type='S') exec sp_droplogin 'ovms_admin'
end
else
begin
select 'SQL Server 2000'
IF EXISTS (SELECT * FROM syslogins WHERE name LIKE '<DOMAIN\My-User>' AND isntname='1') exec sp_revokelogin [<DOMAIN\My-User>]
IF EXISTS (SELECT * FROM syslogins WHERE name LIKE '<DOMAIN\My2-User>' AND isntname='1') exec sp_revokelogin [<DOMAIN\My2-User>]
if (SELECT name FROM sysdatabases WHERE name = 'openview') = 'openview'
begin
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovdb_user' AND isntname='0')
begin
exec openview..sp_droprolemember 'role_ovdb_user', 'ovdb_user'
exec openview..sp_droprole 'role_ovdb_user'
end
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovdb_user' AND isntname='0') exec openview..sp_dropuser 'ovdb_user'
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovms_adm' AND isntname='1') exec openview..sp_dropuser 'ovms_adm'
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovms_admin' AND isntname='0') exec openview..sp_dropuser 'ovms_admin'
IF EXISTS (SELECT name,isntname FROM openview..sysusers WHERE name LIKE 'ovms_deleg' AND isntname='1') exec openview..sp_dropuser 'ovms_deleg'
use master
end
end
go
/* Drop entire database. Files will be removed. */
if (select name from sysdatabases where name = 'mydb') = 'mydb'
drop database mydb
go
Now, when I execute this script, SQL Server shows the following error message:
"The user owns objects in the database and can not be removed." The original Message is in german, this is only a translation.
Does anyone know how I can solve this and force the removal of the users?
Thanks you in advance for your help!
Chris
Comment