Essential Scripts related to SQL Server Users and Logins

Introduction

In this blog post, we will explore six (6) essential scripts that will revolutionize your SQL Server management, ranging from retrieving login details and user lists to fixing orphaned users and auditing SQL Server logins. Let’s dive in!

Script to find all login details
Script to find List of Users per Role
Script to find List of special users per database
Script to Fix orphan users of current DB
Script to Fix orphan User for all existing database
Script for Audit SQL Server Logins

Script to find all login details

EXEC sp_helplogins;

SQL

Script to find List of Users per Role

EXEC sp_helpsrvrolemember;

SQL

Script to find List of special users per database

DECLARE @name sysname,
        @SQL nvarchar(600)

IF EXISTS (SELECT [id] FROM tempdb..sysobjects WHERE [id] = OBJECT_ID('tempdb..#tmpTable'))
    DROP TABLE #tmpTable

CREATE TABLE #tmpTable (
    [DATABASE_NAME] sysname NOT NULL,
    [USER_NAME] sysname NOT NULL,
    [ROLE_NAME] sysname NOT NULL
)

DECLARE c1 CURSOR FOR
    SELECT name FROM master.dbo.sysdatabases

OPEN c1
FETCH c1 INTO @name

WHILE @@FETCH_STATUS >= 0
BEGIN
    SELECT @SQL =
        'INSERT INTO #tmpTable
        SELECT N''' + @name + ''', a.name, c.name
        FROM ' + QUOTENAME(@name) + '.dbo.sysusers a
        JOIN ' + QUOTENAME(@name) + '.dbo.sysmembers b ON b.memberuid = a.uid
        JOIN ' + QUOTENAME(@name) + '.dbo.sysusers c ON c.uid = b.groupuid
        WHERE a.name != ''dbo'''

    /* Insert row for each database */
    EXECUTE (@SQL)

    FETCH c1 INTO @name
END

CLOSE c1
DEALLOCATE c1

SELECT * FROM #tmpTable

DROP TABLE #tmpTable

SQL

Script to Fix orphan users of current DB

USE AdventureWorks2016

DECLARE @username varchar(25)
DECLARE fixusers CURSOR FOR
    SELECT UserName = name
    FROM sysusers
    WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)
    AND suser_sname(sid) IS NULL
    ORDER BY name

OPEN fixusers
FETCH NEXT FROM fixusers INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @username = 'dbo'
    BEGIN
        EXEC sp_changedbowner 'sa'
    END
    ELSE
    BEGIN
        EXEC sp_change_users_login 'update_one', @username, @username
    END

    FETCH NEXT FROM fixusers INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers

SQL

Script to Fix orphan User for all existing database

DECLARE @DBName NVARCHAR(255)
DECLARE orphanuser_curDB CURSOR FOR
SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model') ORDER BY name

OPEN orphanuser_curDB
FETCH NEXT FROM orphanuser_curDB INTO @DBName

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @sql VARCHAR(5000)

    SET @sql = 'USE [' + @DBName + ']
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + '': user name being resynced''

                    EXEC sp_change_users_login ''Update_one'', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur'

    PRINT @sql
    FETCH NEXT FROM orphanuser_curDB INTO @DBName
END

CLOSE orphanuser_curDB
DEALLOCATE orphanuser_curDB
GO

SQL

Below is the output of the above TSQL, may use to fix Orphan User.

USE [AdventureWorks2008R2]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur
USE [AdventureWorks2012]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur
USE [AdventureWorks2014]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur
USE [AdventureWorks2016]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur
USE [AdventureWorks2017]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur
USE [AdventureWorks2019]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur
USE [AdventureWorks2022]
                DECLARE @UserName NVARCHAR(255)
                DECLARE orphanuser_cur CURSOR FOR
                SELECT UserName = name
                FROM sysusers
                WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0) AND suser_sname(sid) IS NULL
                ORDER BY name

                OPEN orphanuser_cur
                FETCH NEXT FROM orphanuser_cur INTO @UserName

                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    PRINT @UserName + ': user name being resynced'

                    EXEC sp_change_users_login 'Update_one', @UserName, @UserName

                    FETCH NEXT FROM orphanuser_cur INTO @UserName
                END

                CLOSE orphanuser_cur
                DEALLOCATE orphanuser_cur

SQL

Script for Audit SQL Server Logins

/*
Audit SQL Server user ID
This script will generate an audit of SQL Server logins, as wellas a listing of the database user ID's and the SQL Server login that each DB user ID maps to.

In the database user ID results, [Server Login] = '** Orphaned **' indicates that there is no matching Server login. This script was originally designed for SQL 2000, but works just as well in SQL 2005.
*/
         -- Drop temporary tables if they already exist
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type = 'U')
   DROP TABLE ##Users

GO

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type = 'U')
   DROP TABLE ##DBUsers

GO

-- Use the master database
USE master

-- Declare variables
DECLARE @DBName VARCHAR(32)
DECLARE @SQLCmd VARCHAR(1024)

-- Get the SQL Server logins
SELECT sid,
       loginname AS [Login Name],
       dbname AS [Default Database],
       CASE isntname
          WHEN 1 THEN 'AD Login'
          ELSE 'SQL Login'
       END AS [Login Type],
       CASE
          WHEN isntgroup = 1 THEN 'AD Group'
          WHEN isntuser = 1 THEN 'AD User'
          ELSE ''
       END AS [AD Login Type],
       CASE sysadmin
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [sysadmin],
       CASE [securityadmin]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [securityadmin],
       CASE [serveradmin]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [serveradmin],
       CASE [setupadmin]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [setupadmin],
       CASE [processadmin]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [processadmin],
       CASE [diskadmin]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [diskadmin],
       CASE [dbcreator]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [dbcreator],
       CASE [bulkadmin]
          WHEN 1 THEN 'Yes'
          ELSE 'No'
       END AS [bulkadmin]
INTO ##Users
FROM dbo.syslogins

-- Retrieve the SQL Server logins
SELECT [Login Name],
       [Default Database],
       [Login Type],
       [AD Login Type],
       [sysadmin],
       [securityadmin],
       [serveradmin],
       [setupadmin],
       [processadmin],
       [diskadmin],
       [dbcreator],
       [bulkadmin]
FROM ##Users
ORDER BY [Login Type], [AD Login Type], [Login Name]

-- Create a temporary table to store database user information
CREATE TABLE ##DBUsers (
   [Database] VARCHAR(64),
   [Database User ID] VARCHAR(64),
   [Server Login] VARCHAR(64),
   [Database Role] VARCHAR(64)
)

-- Cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

-- Open the cursor and fetch the first database name
OPEN csrDB
FETCH NEXT FROM csrDB INTO @DBName

-- Loop through the databases
WHILE @@FETCH_STATUS = 0
BEGIN
   -- Construct the dynamic SQL command to insert database user information into the temporary table
   SELECT @SQLCmd = 'INSERT ##DBUsers ' +
                    'SELECT ''' + @DBName + ''' AS [Database],' +
                    '       su.[name] AS [Database User ID],' +
                    '       COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login],' +
                    '       COALESCE (sug.name, ''Public'') AS [Database Role]' +
                    'FROM [' + @DBName + '].[dbo].[sysusers] su ' +
                    '   LEFT OUTER JOIN ##Users u ' +
                    '      ON su.sid = u.sid ' +
                    '   LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
                    '               INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' +
                    '                  ON sm.groupuid = sug.uid) ' +
                    '      ON su.uid = sm.memberuid ' +
                    'WHERE su.hasdbaccess = 1 ' +
                    '  AND su.[name] != ''dbo'''

   -- Execute the dynamic SQL command
   EXEC (@SQLCmd)

   -- Fetch the next database name
   FETCH NEXT FROM csrDB INTO @DBName
END

-- Close and deallocate the cursor
CLOSE csrDB
DEALLOCATE csrDB

-- Retrieve the final results from the temporary table
SELECT *
FROM ##DBUsers
ORDER BY [Database], [Database User ID]

-- Clean up - delete the temporary tables
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type = 'U')
   DROP TABLE ##Users

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type = 'U')
   DROP TABLE ##DBUsers

GO

SQL