locked
SQL 2008 R2 restored DB to new server: "The database principal owns a schema in the database, and cannot be dropped. (.Net SqlClient Data Provider)" RRS feed

  • Question

  • Hello. Disclosure: I'm very far from a DBA ;)

    I've been tasked with creating a new SQL server and restoring 60 DBs from the old, which I've done. I'm testing one of the applications, and getting errors related to one of the logins, TestAdmin.

    I checked the DB Security > Users, and found the TestAdmin account. However, the account is not in this instance's Security > Logins. Tried to add the user, but got error "User, group, or role 'TestAdmin' already exists in the current database. (Microsoft SQL Server, Error:15023)"

    When I tried to remove TestAdmin from the DB Security > Users, I got the database principal owns a schema error from the title.

    I've also noticed that the original SQL server has around 100 Security > Logins. I'm concerned that this is going to be an issue with every db.

    Guidance appreciated!

    Thanks,

    Scott

    Wednesday, September 3, 2014 9:36 PM

Answers

  • Hi Scott,

    I Guess you have run into orphaned user situation. Please use this script to identify if there are any Orphaned users

    CREATE TABLE ##ORPHANUSER 
    ( 
    DBNAME VARCHAR(100), 
    USERNAME VARCHAR(100), 
    CREATEDATE VARCHAR(100), 
    USERTYPE VARCHAR(100) 
    ) 
     
    EXEC SP_MSFOREACHDB' USE [?] 
    INSERT INTO ##ORPHANUSER 
    SELECT DB_NAME() DBNAME, NAME,CREATEDATE, 
    (CASE  
    WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN'' 
    WHEN ISNTGROUP = 1 THEN ''NT GROUP'' 
    WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN'' 
    END) [LOGIN TYPE] FROM sys.sysusers 
    WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND 
    SID NOT IN (SELECT SID FROM sys.syslogins)' 
    
     
    SELECT * FROM ##ORPHANUSER 
     
    DROP TABLE ##ORPHANUSER 
    
    

    You probaly might see some orphaned users here that are both SQL and WINDOWS logins. There could be two scenarios here for an orphaned user. Either the login is missing @Server security or SID mismatch between the user and the login.                                                                                                                                                 

    The SID mismatch can be resolved by  using the following statement which you probably know already

     USE <database_name>;
    GO
    sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
       @LoginName='<login_name>';
    GO
    

    If there is a login missing, Creating one will solve the issue for windows logins with out having to resolve the SID mismatch. But if it is a SQL login the above step might have to be followed.

    Coming to the other part of your question, Yes you can't delete user that owns a schema or a role in a database. Two options here to resolve. Either drop the roles/schema before you drop the user which we genreally don't recommend as that could be disastrous. So please use the below comand to alter the schema owner so that the user can be dropped

    USE DBNAME ; SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('user'); ALTER AUTHORIZATION ON SCHEMA::<Schema_name> TO dbo

    -- Similar command be used to alter the role authorization

    Hopefully this will help you scott.

    SQL Gurus, Please correct me if i am wrong adressing this issue.

    Thanks

    Bhanu 

    • Proposed as answer by Sofiya Li Thursday, September 4, 2014 9:47 AM
    • Marked as answer by scogordo Monday, September 15, 2014 3:49 PM
    Wednesday, September 3, 2014 10:43 PM

All replies

  • Issue : you are trying to drop a user owning a schema. 
    In order to drop the user, you have to find the schema that’s assigned and then transfer the ownership to another user/role or to drop it.



    SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('testuser')
    --Then, use the names found from the above query below in place of the SchemaName below. And drop your user.
      ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
    GO
    DROP USER testuser

    • Proposed as answer by Sofiya Li Thursday, September 4, 2014 9:51 AM
    Wednesday, September 3, 2014 10:19 PM
  • Please try above option and update accordingly 

    Wednesday, September 3, 2014 10:20 PM
  • Hi Scott,

    I Guess you have run into orphaned user situation. Please use this script to identify if there are any Orphaned users

    CREATE TABLE ##ORPHANUSER 
    ( 
    DBNAME VARCHAR(100), 
    USERNAME VARCHAR(100), 
    CREATEDATE VARCHAR(100), 
    USERTYPE VARCHAR(100) 
    ) 
     
    EXEC SP_MSFOREACHDB' USE [?] 
    INSERT INTO ##ORPHANUSER 
    SELECT DB_NAME() DBNAME, NAME,CREATEDATE, 
    (CASE  
    WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN'' 
    WHEN ISNTGROUP = 1 THEN ''NT GROUP'' 
    WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN'' 
    END) [LOGIN TYPE] FROM sys.sysusers 
    WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND 
    SID NOT IN (SELECT SID FROM sys.syslogins)' 
    
     
    SELECT * FROM ##ORPHANUSER 
     
    DROP TABLE ##ORPHANUSER 
    
    

    You probaly might see some orphaned users here that are both SQL and WINDOWS logins. There could be two scenarios here for an orphaned user. Either the login is missing @Server security or SID mismatch between the user and the login.                                                                                                                                                 

    The SID mismatch can be resolved by  using the following statement which you probably know already

     USE <database_name>;
    GO
    sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
       @LoginName='<login_name>';
    GO
    

    If there is a login missing, Creating one will solve the issue for windows logins with out having to resolve the SID mismatch. But if it is a SQL login the above step might have to be followed.

    Coming to the other part of your question, Yes you can't delete user that owns a schema or a role in a database. Two options here to resolve. Either drop the roles/schema before you drop the user which we genreally don't recommend as that could be disastrous. So please use the below comand to alter the schema owner so that the user can be dropped

    USE DBNAME ; SELECT s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('user'); ALTER AUTHORIZATION ON SCHEMA::<Schema_name> TO dbo

    -- Similar command be used to alter the role authorization

    Hopefully this will help you scott.

    SQL Gurus, Please correct me if i am wrong adressing this issue.

    Thanks

    Bhanu 

    • Proposed as answer by Sofiya Li Thursday, September 4, 2014 9:47 AM
    • Marked as answer by scogordo Monday, September 15, 2014 3:49 PM
    Wednesday, September 3, 2014 10:43 PM
  • Thank you both very much. I'll try this out today. I did run the orphaned user script which turned up 233 results!

    I wasn't familiar with the SID mismatch script. This will need to be done for each orphaned SQL login for each DB? Your following sentence suggests that WIndows logins might be affected too?

    I need to avoid creating new logins, as this needs to be a replica of another SQL server.

    Once these users have been dropped from the schema, I can readd them when I recreate the SQL user in SSMS, correct.

    Again, thanks for the guidance.

    Scott

    Thursday, September 4, 2014 10:59 AM
  • Hi Scott,

    If you observe the result set of the orphaned user query in the USERTYPE column, 'SQL LOGIN' refers to Login with SQL Authentication and 'NT LOGIN' refers to windows login.

    For 'SQL Login' you may have to create both the login if it is missing @ Server Security and run the SID mismatch script against each database that has this login as a user.

    In my understanding for 'NT Login' creating the login should be enough @ server security to resolve the orphaned user issue against all the databases that the Login is mapped to.

    I would suggest you to run the orphaned user script at the  Source server and check if you have the similar number of results. If you can get similar number of results you can ignore the whole result set and just fix those users that are complaining for now and do a clean up later on as this could be lot time consuming.                        I have some servers with similar amount of orphaned users as i think the security gets too complex over the years of adding and removing users out of databases. I just ignore them - This is just my understanding though

    Coming to your main issue for TestAdmin Login - Is this a SQL or Windows Login? Also can you please check if this user exists as a Login @Server Security. Also please post the error if it still persists

    Hope this helps

    Many Thanks

    Bhanu


    • Edited by bhanu_nz Thursday, September 4, 2014 10:18 PM
    Thursday, September 4, 2014 10:16 PM