Generalized query to find whether the database user owns a schema

Question

We have a lot of DBUsers in our database. We have to clean up all those users from the database. When I tried to drop the user from the DB it failed due to the following error

Msg 15138, Level 16, State 1, Line 2 The database principal owns a schema in the database, and cannot be dropped.

So I found the solution for this i.e I changed the ownership of the schema to dbo. Now I can drop the user by using below script

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('myUser')
ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo 
GO
DROP USER myUser

But I want to drop a number of users so I need to generate generalized script. I am using SQL Server 2008

Hocker

You should specify the database you are using. – Affinal

Answer 1

This will give you a complete list of schemas against the users which own them:

SELECT db.name AS [DB User], s.name AS [Schema]
FROM sys.database_principals db
JOIN sys.schemas s ON s.principal_id = db.principal_id

Amoeboid

Answer 2

Generally speaking, these schemas are as unwanted as their owning users. It is therefore sensible to drop them before dropping their users. This is realtively simple if they have no objects.

This script drops orphaned users, first deleting any empty schemas that they own. @Debug = 1 means the commands will only be printed. Setting @Debug to 0 will cause the commands to be executed.

SET NOCOUNT ON;

DECLARE @debug BIT;
SELECT @debug = 1;

DECLARE @commands TABLE
(
    id INT IDENTITY(1, 1) NOT NULL,
    command sysname NOT NULL
);

INSERT @commands
(
    command
)
SELECT CASE
           WHEN NOT EXISTS
                    (
                        SELECT 1 FROM sys.objects so WHERE so.schema_id = sch.schema_id
                    ) THEN
               N'DROP SCHEMA [' + sch.name + N'];'
           ELSE
               N'PRINT (''Schema ' + sch.name + ' has objects and cannot be dropped'')'
       END
FROM sys.schemas sch
WHERE EXISTS
(
    SELECT 1
    FROM sys.database_principals dp
    WHERE sch.principal_id = dp.principal_id
          AND NOT EXISTS
    (
        SELECT 1 FROM sys.server_principals sp WHERE sp.sid = dp.sid
    )
          AND type NOT IN ( 'R' )
          AND
          (
              sid IS NOT NULL
              AND sid <> 0
          )
);

INSERT @commands
(
    command
)
SELECT N'DROP USER [' + name + N'];'
FROM sys.database_principals dp
WHERE NOT EXISTS
(
    SELECT 1 FROM sys.server_principals sp WHERE sp.sid = dp.sid
)
      AND type NOT IN ( 'R' )
      AND
      (
          sid IS NOT NULL
          AND sid <> 0
      );

DECLARE @command sysname;
DECLARE @loop INT,
        @loopmax INT;
SELECT @loop = 1,
       @loopmax = MAX(id)
FROM @commands;

WHILE @loop <= @loopmax
BEGIN
    SELECT @command = command
    FROM @commands
    WHERE id = @loop;

    IF @debug = 0
        EXECUTE (@command);
    ELSE
        PRINT (@command);

    SELECT @loop = @loop + 1;
END;

Pudendum

Answer 3

I liked the query from @waitForPete because it checks if the schema has anything in it, and drops it if no.

obviously that is not what you always want but it has its uses too.

this is my query to print out the schemas that are owned by a user that you want to drop, so you can drop that particular user:

I have wrapped the query inside a cursor just so it gives me the print of the script.

I do also check if the schema exists, in case I am applying this script to a different database or the same database after a restore.

PRINT ' '
PRINT ' '
PRINT '-------------------------------------------------------------------'
PRINT '-- Server and DB: ' + @@servername + '.' + db_name()
PRINT '-- script to apply permissions.'
PRINT '-- Marcelo Miorelli v.17-Jul-2024'
PRINT '-- ' + 'These permissions were taken on ' + DateName( weekday , GETDATE()) + ',' +  CONVERT(VARCHAR(30),GETDATE(),113)
PRINT '-------------------------------------------------------------------'
PRINT ' '

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE
@DatabaseUserName [sysname], 
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@SchemaName [varchar](8000),
@MEmberName [varchar](800),
@ObjectID [int],
@ObjectName [varchar](261)

PRINT 'PRINT   ' + '''--Server and DB:'' + @@servername + ''.'' + db_name()'
PRINT 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'
PRINT 'SET NOCOUNT ON'
PRINT ''

PRINT '-- ——— CHANGE SCHEMA OWNER TO DBO ————————————–'

DECLARE _Dschemas
CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR 
      SELECT the_script FROM
        (
         SELECT  schema_name = s.name
                ,s.principal_id
                ,schema_owner = USER_NAME(s.principal_id) 
                ,the_script = 'IF EXISTS(SELECT * FROM sys.schemas s WITH(NOLOCK) WHERE quotename(s.name) = ' 
                            + '''[' + s.name + ']''  COLLATE SQL_Latin1_General_CP1_CI_AS)' 
                            + ' ALTER AUTHORIZATION ON SCHEMA::' + quotename(s.name) 
                            + ' TO [dbo]' 
           FROM sys.schemas s WITH (NOLOCK)
     INNER JOIN sys.database_principals dp WITH (NOLOCK)
             ON s.principal_id = dp.principal_id
          WHERE 1=1
            AND dp.is_fixed_role = 0
            AND s.principal_id > 4
         ) Radhe

OPEN _Dschemas FETCH NEXT FROM _Dschemas INTO @msgStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @msgStatement
FETCH NEXT FROM _Dschemas INTO @msgStatement
END
CLOSE _Dschemas
DEALLOCATE _Dschemas

careful, always check on which server and database you are, see the first line I have printed, this is very important.

stay away from live servers, unless you really need to be there.

wrap everything into begin transaction and rollback or -- commit transaction

Ares