NuRoN Consulting, INC.

Blog > SQL Tools > Template Scripts > Users > Cleanup unused database users

Template Script: Users\Cleanup unused database users.sql

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM (
    SELECT 'USE [' + a.dbname + ']
GO
' + Util.dbo.StringConcat('DROP SCHEMA [' + USERNAME + ']
GO
' + dropsql + '
GO
', '') AS SQL
    FROM dba.Metadata.PrincipalsAll a
    WHERE --A.DbName ='PIN'
        a.Type IN (
            'u',
            'g'
            )
        AND NOT EXISTS (
            SELECT *
            FROM dba.Metadata.PermissionsrecursiveAll p
            WHERE p.dbname = a.dbname
                AND a.UserName = p.membername
                AND p.Permission NOT IN (
                    'Connect',
                    'SHOWPLAN',
                    'VIEW DEFINITION',
                    'VIEW DATABASE STATE'
                    )
            )
    GROUP BY a.dbname
    ) K

EXEC Util.dbo.PrintLargeText @SQL

Description for Template Script: Users\Cleanup unused database users.sql

Todo