This give me a lot of orphan database users.
To make thing more entertaining some of the databases has compability level 80, and this can not be changed.
To get a list of orphan database users, I have to combine two methods. This is because the compability level 80 databases does not have the system view database_principals.
DECLARE @db_name AS nvarchar(128); -- Database name
DECLARE @_msg AS nvarchar(2047); -- Execution message
DECLARE @_cmd AS nvarchar(MAX); -- Dynamic command
CREATE TABLE ##orphan_user (
[db_name] nvarchar(128) NOT NULL,
[user_name] nvarchar(128) NOT NULL
);
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [database_id] > 4;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT [compatibility_level] FROM [master].[sys].[databases] WHERE [name] = @db_name) < 90 BEGIN SET @_msg = N'The database [' + @db_name + N'] is SQL Server 2000 or older.'; SET @_cmd = 'INSERT INTO ##orphan_user ' + N'SELECT ''' + @db_name + N''' AS [db_name], [sysusers].[name] AS [user_name] ' + N'FROM [' + @db_name + N'].[dbo].[sysusers] ' + N'LEFT OUTER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid] ' + N'WHERE [sysusers].[uid] > 4 AND [sysusers].[issqlrole]=0 AND [sysusers].[isapprole]=0 AND [master].[dbo].[syslogins].[sid] IS NULL;';
END
ELSE
BEGIN
SET @_msg = N'The database [' + @db_name + N'] is SQL Server 2005 or newer.';
SET @_cmd = N'INSERT INTO ##orphan_user ' +
N'SELECT ''' + @db_name + N''' AS [db_name], [database_principals].[name] AS [user_name] ' +
N'FROM [' + @db_name + N'].[sys].[database_principals] ' +
N'WHERE [database_principals].[type] IN (''S'',''U'',''G'') ' +
N'AND [database_principals].[is_fixed_role] = 0 ' +
N'AND [database_principals].[name] NOT IN (N''dbo'', N''guest'', N''sys'', N''INFORMATION_SCHEMA'');';
END
RAISERROR(@_msg,0,0) WITH NOWAIT;
EXECUTE (@_cmd);
FETCH NEXT FROM db_cursor INTO @db_name;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
SELECT * FROM [##orphan_user];
SELECT DISTINCT [user_name] FROM [##orphan_user]
ORDER BY [user_name] DESC
DROP TABLE [##orphan_user];I execute only a part of the script where I fill the temporary table. Then I can filter and sort the data to handle the logins.
Posts
