Listing All Usernames and Roles for All Databases in SQL Server

As a SQL Server DBA, managing and auditing user roles and permissions across multiple databases is a critical task. Ensuring that each user has the appropriate access rights is essential for security and compliance. In this post, we’ll share a script that allows you to list all usernames and their associated roles across all databases in a SQL Server instance.

Why is This Important?

• Security & Compliance: Regular audits help prevent unauthorized access.

• Troubleshooting Access Issues: Quickly identify missing or incorrect permissions.

• User Management: Helps maintain a structured approach to assigning roles and logins.

This script retrieves user information, including their associated roles, from all databases on a SQL Server instance. It works for different SQL Server versions and dynamically queries all databases using sp_MSForEachdb.

SQL Script: Listing Usernames and Roles

USE MASTER

GO

BEGIN

DECLARE @SQLVerNo INT;

SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);

IF @SQLVerNo >= 9

    IF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%')

        DROP TABLE #TUser

ELSE

IF @SQLVerNo = 8

BEGIN

    IF EXISTS (SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%')

        DROP TABLE #TUser

END

CREATE TABLE #TUser (

    ServerName    varchar(256),

    DBName        SYSNAME,

    [Name]        SYSNAME,

    GroupName     SYSNAME NULL,

    LoginName     SYSNAME NULL,

    default_database_name  SYSNAME NULL,

    default_schema_name    VARCHAR(256) NULL,

    Principal_id  INT,

    [sid]         VARBINARY(85))

IF @SQLVerNo = 8

BEGIN

 INSERT INTO #TUser

 EXEC sp_MSForEachdb

 '

  SELECT

    @@SERVERNAME,

    ''?'' as DBName,

    u.name As UserName,

    CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,

    l.name AS LoginName,

    NULL AS Default_db_Name,

    NULL as default_Schema_name,

    u.uid,

    u.sid

  FROM [?].dbo.sysUsers u

    LEFT JOIN ([?].dbo.sysMembers m

    JOIN [?].dbo.sysUsers r

    ON m.groupuid = r.uid)

    ON m.memberuid = u.uid

    LEFT JOIN dbo.sysLogins l

    ON u.sid = l.sid

  WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1

  ORDER BY u.name

 '

END

ELSE

IF @SQLVerNo >= 9

BEGIN

 INSERT INTO #TUser

 EXEC sp_MSForEachdb

 '

  SELECT

    @@SERVERNAME,

    ''?'',

    u.name,

    CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,

    l.name LoginName,

    l.default_database_name,

    u.default_schema_name,

    u.principal_id,

    u.sid

  FROM [?].sys.database_principals u

    LEFT JOIN ([?].sys.database_role_members m

    JOIN [?].sys.database_principals r

    ON m.role_principal_id = r.principal_id)

    ON m.member_principal_id = u.principal_id

    LEFT JOIN [?].sys.server_principals l

    ON u.sid = l.sid

  WHERE u.TYPE <> ''R''

  order by u.name

  '

END

SELECT *

FROM #TUser

ORDER BY DBName, [name], GroupName

DROP TABLE #TUser

END

How to Retrieve Disabled SQL Server Jobs

In SQL Server, SQL Agent Jobs play a crucial role in automating database maintenance tasks, backups, and other scheduled activities. However, sometimes jobs may be disabled due to administrative decisions, troubleshooting needs, or unexpected issues. As a DBA, it’s essential to track and manage these disabled jobs to ensure critical processes are not inadvertently left inactive.

In this blog post, we will look at a simple yet effective SQL query to retrieve a list of all disabled jobs in SQL Server.

Query to Fetch Disabled Jobs Below is a straightforward SQL query to identify all jobs that are currently disabled:

SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name;

Notes:

• msdb.dbo.sysjobs: This system table stores metadata for all SQL Server Agent jobs.

• enabled = 0: The enabled column indicates whether a job is active (1) or disabled (0). By filtering on 0, we retrieve only disabled jobs.

Automating the Monitoring Process To proactively monitor disabled jobs, consider setting up an automated alert or report. Here’s an approach using SQL Server Agent:

Create a SQL Server Agent Job

• Schedule the above query to run at regular intervals.
• Store the results in a table or send an email alert.

Using Database Mail for Notifications

DECLARE @body NVARCHAR(MAX);
SET @body = (SELECT STRING_AGG(name, ', ') FROM msdb.dbo.sysjobs WHERE enabled = 0);

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourMailProfile',
    @recipients = 'dba_team@example.com',
    @subject = 'Disabled SQL Server Jobs Alert',
    @body = 'The following jobs are currently disabled: ' + @body;

Monitoring SQL Server Backup and Restore Progress

Managing database backups and restores is a critical aspect of SQL Server administration. When dealing with large databases, it becomes essential to track the progress of ongoing backup and restore operations to estimate completion time and monitor performance. This blog post provides a structured SQL script to monitor these operations effectively.

Why Monitor Backup and Restore Progress?

• Ensures visibility into long-running operations

• Helps estimate completion time for better planning

• Identifies potential performance bottlenecks

• Prevents unnecessary disruptions by avoiding premature interventions

SQL Script to Monitor Backup and Restore Progress Use the following SQL script to track the percentage completion, elapsed time, and estimated completion time of backup and restore operations in SQL Server:

SELECT

    r.session_id AS [Session_Id],

    r.command AS [Command],

    CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete],

    GETDATE() AS [Current Time],

    CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time],

    CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min],

    CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min],

    CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours],

    CONVERT(VARCHAR(1000), (

        SELECT SUBSTRING(TEXT, r.statement_start_offset / 2,

            CASE WHEN r.statement_end_offset = -1 THEN 1000

            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 END)

        FROM sys.dm_exec_sql_text(sql_handle)

    )) AS [Statement Text]

FROM sys.dm_exec_requests r

WHERE r.command LIKE 'RESTORE%'

   OR r.command LIKE 'BACKUP%'

   OR r.command LIKE 'DbccFilesCompact'

   OR r.command LIKE 'DbccSpaceReclaim';

How to Verify SQL Server Backups with a Simple Query

Ensuring database backups are completed successfully is a critical task for every SQL Server DBA. In this post, I’ll share a SQL script that quickly checks the latest full backup status of all databases on a SQL Server instance.

SQL Script to Verify Backup Completion

• The following script provides:
• The server name
• The database name
• The last backup date
• The backup status (Completed/Not Taken)
• The backup file name

SET NOCOUNT ON  

SELECT 'SERVER NAME : ' + @@SERVERNAME  

SELECT  
    SUBSTRING(s.name, 1, 40) AS 'Database Name',  
    CAST(b.backup_start_date AS CHAR(25)) AS 'Last Backup Date',  
    CASE  
        WHEN b.backup_start_date > DATEADD(dd, -1, GETDATE()) THEN 'Backup Completed'  
        WHEN b.backup_start_date > DATEADD(dd, -7, GETDATE()) THEN 'Not taken'  
        ELSE 'Not taken'  
    END AS 'Status',  
    SUBSTRING(m.physical_device_name, 1, 100) AS 'Backup File Name'  
FROM master..sysdatabases s  
LEFT OUTER JOIN msdb..backupset b  
    ON s.name = b.database_name  
    AND b.backup_start_date = (  
        SELECT MAX(backup_start_date)  
        FROM msdb..backupset  
        WHERE database_name = b.database_name  
        AND type = 'D' -- Full database backups only  
    )  
LEFT OUTER JOIN msdb..backupmediafamily m  
    ON m.media_set_id = b.media_set_id  
    AND m.physical_device_name = (  
        SELECT MAX(physical_device_name)  
        FROM msdb..backupmediafamily  
        WHERE media_set_id = b.media_set_id  
    )  
WHERE s.name <> 'tempdb'  
ORDER BY s.name  


The script retrieves the most recent full backup (type = 'D') for each database.
It checks if the last backup was completed within the last 24 hours.
If no backup is found in the last 24 hours, it flags it as Not Taken.
The output includes the backup file name for easy tracking.

How to Remove Stuck SQL Server Services After a Failed Cluster Installation

Problem:
A friend encountered an issue while installing SQL Server in a clustered environment. The installation stopped midway, and he was unable to remove SQL Server using Add/Remove Programs or the SQL Server Setup. Even after manually deleting registry entries and folders, SQL Server services were still visible in Services.msc.

Solution:
If you face a similar issue, follow these steps to completely remove SQL Server services:

Step 1: Delete SQL Server Services Using SC Command

Open Command Prompt as Administrator and run the following commands:

sc delete sqlserveragent
sc delete mssqlserver

This will remove the SQL Server Agent and SQL Server (MSSQLSERVER) services. If you are using a named instance, replace mssqlserver with mssql$<InstanceName>.

Step 2: Delete SQL Server Services from Registry

Since the services might still be registered in the Windows registry, follow these steps to clean up:

1. Press Win + R, type regedit, and hit Enter.
2. Navigate to the following path:

   
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
   

3. Look for entries related to SQL Server, such as:
   • MSSQLSERVER
   • SQLSERVERAGENT
   • MSSQL$InstanceName (for named instances)
4. Right-click each SQL-related entry and Delete them.
5. Close Registry Editor and restart the system.

After performing these steps, the SQL Server services should be completely removed from the system.