SQL Server maintenance can be a pain. There are so many moving parts that checking every single possible maintenance point takes forever. Being the lazy DBA that I am, I prefer to automate my maintenance tasks. It saves me time and makes sure I don’t miss anything. This little stored procedure, run in a SQL Agent job, tracks down those pesky Database Roles that have no logins assigned to them. Rather than auto-deleting the roles, I prefer a manual verification before manual deletion, so this proc only sends an email list of unused database roles.
CREATE PROCEDURE [dbo].[spDQ_UnusedDatabaseRoles]
@emails VARCHAR(255) = NULL
AS
DESCRIPTION: This is a Data Quality proc created to list all user-created database roles that do not
currently have a login mapped to them.
SET NOCOUNT ON;
--check if temp table exists and drop
IF (SELECT Object_ID('tempdb..#Databases') ) IS NOT NULL
DROP TABLE #Databases;
-- create table and load db names
IF (SELECT Object_ID('tempdb..##UnusedDatabaseRoles') ) IS NOT NULL
DROP TABLE ##UnusedDatabaseRoles;
CREATE TABLE #Databases (DatabaseID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, DBName VARCHAR(50));
INSERT INTO #Databases (DBname)
SELECT [Name] AS DBname
FROM MASTER.sys.databases
WHERE [Name] NOT IN ('model', 'tempdb')
AND source_Database_ID IS NULL;
--Do not check Model or TempDB databases, and ignore all snapshots
CREATE TABLE ##UnusedDatabaseRoles (DBName VARCHAR(50), RoleName VARCHAR(50));
-- declare/init vars
DECLARE @LoopCounter int, @DBName varCHAR(100), @sqlstmt varCHAR(5000);
SET @LoopCounter = 1;
WHILE @LoopCounter 0
AND RP.Name NOT IN (''db_dtsadmin'',''db_dtsltduser'',''db_dtsoperator'')';
--This last line allows the DBA to skip any system database level roles that will never have users mapped to them
--PRINT @sqlstmt; --debug syntax
EXECUTE (@sqlstmt);
SET @LoopCounter = @LoopCounter + 1;
END;
DECLARE @profile VARCHAR(50), @dsql VARCHAR(2000), @filename VARCHAR(50), @rwcnt INT = 0;
IF (@emails IS NULL)
BEGIN
SET @emails='DBAeMail@myCompany.com';
END;
SELECT @profile = CASE @@SERVERNAME WHEN 'Dev' THEN 'MyCompany_Dev@MyCompany.com'
WHEN 'Test' THEN 'MyCompany_Test@MyCompany.com'
WHEN 'QC' THEN 'MyCompany_QC@MyCompany.com'
WHEN 'Prod' THEN 'MyCompany@MyCompany.com' END;
--Sets the DB Mail profile for a multiple Instance environment.
--The CASE statement is not needed for Single Server / Environment setups or single (default) profile setups
SET @filename = 'UnusedDatabaseRoles_' + LEFT(CONVERT(VARCHAR(20),GETDATE(),112),8) + '.txt';
SELECT @rwcnt = COUNT(RoleName)
FROM ##UnusedDatabaseRoles;
IF (@rwcnt>0)
BEGIN
SET @dSQL='SET NOCOUNT ON;
SELECT DBName, RoleName
FROM ##UnusedDatabaseRoles;
SET NOCOUNT OFF;';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @emails,
@query = @dsql,
@subject = 'Weekly DQ Report of Unused Database Roles',
@body = 'The Database Roles in the below file are not being used by any login. Please that these roles are still required. This is an automated email. Do NOT directly respond or reply.
If you have any questions, please contact the DBA team at DBAeMail@MyCompany.com.',
@attach_query_result_as_file = 1,
@query_attachment_filename =@filename;
END;
