DBAs often need a quick way to extract SQL Server security information logins, roles, and permissions into a readable format for audits and reviews.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[SP_Audit_DbPermissions] Script Date: 2/15/2026 11:45:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Audit_DbPermissions]
AS
SET NOCOUNT ON;
-- =============================================
-- Description: List all DBs permission
--
-- =============================================
set nocount on
declare @permission table (
Database_Name sysname,
User_Role_Name sysname,
Account_Type nvarchar(60),
Action_Type nvarchar(128),
Permission nvarchar(60),
ObjectName sysname null,
Object_Type nvarchar(60)
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases where state=0
order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
declare @objects table (obj_id int, obj_type char(2))
insert into @objects
select id, xtype from master.sys.sysobjects
insert into @objects
select object_id, type from sys.objects
SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
case e.obj_type
when ''AF'' then ''Aggregate function (CLR)''
when ''C'' then ''CHECK constraint''
when ''D'' then ''DEFAULT (constraint or stand-alone)''
when ''F'' then ''FOREIGN KEY constraint''
when ''PK'' then ''PRIMARY KEY constraint''
when ''P'' then ''SQL stored procedure''
when ''PC'' then ''Assembly (CLR) stored procedure''
when ''FN'' then ''SQL scalar function''
when ''FS'' then ''Assembly (CLR) scalar function''
when ''FT'' then ''Assembly (CLR) table-valued function''
when ''R'' then ''Rule (old-style, stand-alone)''
when ''RF'' then ''Replication-filter-procedure''
when ''S'' then ''System base table''
when ''SN'' then ''Synonym''
when ''SQ'' then ''Service queue''
when ''TA'' then ''Assembly (CLR) DML trigger''
when ''TR'' then ''SQL DML trigger''
when ''IF'' then ''SQL inline table-valued function''
when ''TF'' then ''SQL table-valued-function''
when ''U'' then ''Table (user-defined)''
when ''UQ'' then ''UNIQUE constraint''
when ''V'' then ''View''
when ''X'' then ''Extended stored procedure''
when ''IT'' then ''Internal table''
end as ''Object Type''
FROM [' + @Next + '].sys.database_principals a
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
left join @objects e on d.major_id = e.obj_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission
--where Action_Type not in ('select','CONNECT REPLICATION','connect','execute','REFERENCES','IMPERSONATE','VIEW DEFINITION','VIEW ANY COLUMN ENCRYPTION KEY DEFINITION','VIEW ANY COLUMN MASTER KEY DEFINITION',
--'VIEW CHANGE TRACKING','VIEW DATABASE STATE')
GO
/****** Object: StoredProcedure [dbo].[SP_Audit_DbRoles] Script Date: 2/15/2026 11:45:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Audit_DbRoles]
AS
SET NOCOUNT ON;
CREATE TABLE #UserPermission
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
TypeOfRole VARCHAR(50)
)
INSERT #UserPermission
EXEC sp_MSforeachdb '
use [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' and DATABASEPROPERTYEX(N''?'',''STATUS'') = ''online''
BEGIN
SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
pp.name as PermissionLevel, pp.type_desc as TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
--select p.name as DBUser, q.name as SrvUser,p.sid as DBSID, q.sid as SrvSID
--from sys.database_principals p
left join sys.server_principals q
on p.name COLLATE DATABASE_DEFAULT = q.name COLLATE DATABASE_DEFAULT
where p.type != "R"
and q.name is not null
END '
--select * from sys.database_principals
SELECT * FROM #UserPermission
DROP TABLE #UserPermission
GO
/****** Object: StoredProcedure [dbo].[SP_Audit_Logins] Script Date: 2/15/2026 11:45:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Audit_Logins]
AS
SET NOCOUNT ON;
--Script to generate Prevleged and Active SQL Accounts –
select ''
as PlatformName,
@@SERVERNAME as Server,
'SQL' as PlatformType,
sl.name as Account,
case when sl.isntgroup = 1 then 'GROUP'
when sl.isntgroup = 0 then 'USER'
end as 'Group',
'' as 'Owner',
case when isnull(sl.sysadmin,0) = 1 then 'Yes'
else 'No' end as 'Privileged?',
case when isnull(l.is_disabled,0) = 0 then 'Yes'
else 'No' end as 'Active Account?',
case when isnull(l.is_policy_checked,0) = 1 or sl.isntname = 1 then 'Yes'
else 'No' end as 'Password Required?',
case when isnull(l.is_expiration_checked, 0 ) = 1 or sl.isntname = 1 then ''
else 'Never' end as 'Password Expiration'
FROM syslogins sl
LEFT OUTER JOIN Sys.sql_logins l on sl.sid = l.sid
WHERE sl.name not like '##%' -- Exluding system generated accounts :
-- ##MS_AgentSigningCertificate##
-- ##MS_PolicyEventProcessingLogin##
-- ##MS_PolicySigningCertificate##
-- ##MS_PolicyTsqlExecutionLogin##
-- ##MS_SmoExtendedSigningCertificate##
-- ##MS_SQLAuthenticatorCertificate##
-- ##MS_SQLReplicationSigningCertificate##
-- ##MS_SQLResourceSigningCertificate##
AND sl.name not in ('NT SERVICE\SQLWriter', -- Excluding build in accounts
'NT SERVICE\Winmgmt',
'NT SERVICE\MSSQLSERVER',
'NT AUTHORITY\SYSTEM',
'NT SERVICE\SQLSERVERAGENT')
ORDER BY sl.name
GO
/****** Object: StoredProcedure [dbo].[SP_Audit_ServerPermissions] Script Date: 2/15/2026 11:45:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Audit_ServerPermissions]
AS
SET NOCOUNT ON;
---Script to generate Server level Roles & membership for all Logins -
SELECT --sys.server_permissions.class
--, sys.server_permissions.class_desc,
granteeserverprincipal.name AS Login_principal_name
--, sys.server_permissions.type
, sys.server_permissions.permission_name
--, sys.server_permissions.state
, sys.server_permissions.state_desc
--, sys.server_permissions.major_id
-- , sys.server_permissions.minor_id
-- , sys.server_permissions.grantee_principal_id
-- , sys.server_permissions.grantor_principal_id
, granteeserverprincipal.type_desc AS grantee_principal_type_desc
--, grantorserverprinicipal.name AS grantor_name
-- , CASE
-- WHEN sys.server_permissions.state = N'W'
-- THEN N'GRANT'
-- ELSE sys.server_permissions.state_desc
-- END + N' ' + sys.server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' TO ' + QUOTENAME(granteeserverprincipal.name) AS permissionstatement
FROM sys.server_principals AS granteeserverprincipal
INNER JOIN sys.server_permissions
ON sys.server_permissions.grantee_principal_id = granteeserverprincipal.principal_id
INNER JOIN sys.server_principals AS grantorserverprinicipal
ON grantorserverprinicipal.principal_id = sys.server_permissions.grantor_principal_id
where permission_name not in ('Connect SQL','Connect')
GO
/****** Object: StoredProcedure [dbo].[SP_Audit_ServerRoles] Script Date: 2/15/2026 11:45:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Audit_ServerRoles]
AS
SET NOCOUNT ON;
SELECT
spU.name
,MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin
,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin
,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin
,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin
,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin
,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin
,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator
,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM
sys.server_principals AS spR
JOIN
sys.server_role_members AS srm
ON
spR.principal_id = srm.role_principal_id
JOIN
sys.server_principals AS spU
ON
srm.member_principal_id = spU.principal_id
WHERE
spR.[type] = 'R'
GROUP BY
spU.name
GO
