[ACCEPTED]-Generalized query to find whether the database user owns a schema-sql-server
Accepted answer
This will give you a complete list of schemas 1 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 8 unwanted as their owning users. It is therefore 7 sensible to drop them before dropping their 6 users. This is realtively simple if they 5 have no objects.
This script drops orphaned 4 users, first deleting any empty schemas 3 that they own. @Debug = 1 means the commands 2 will only be printed. Setting @Debug to 1 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;
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.