Announcement

Collapse
No announcement yet.

Problems removing user from database

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problems removing user from database

    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
    htt://www.platzchr.de

  • #2
    If you are going to drop (=delete) a database, why do you want to drop the users before?
    The result is either the same, it's all gone.

    Olaf
    Olaf Helper

    <Blog> <Xing>
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich

    Comment

    Working...
    X