Generalized query to find whether the database user owns a schema

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 ownersh开发者_运维百科ip 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

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

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;