>>Script Language and Platform: SQL Server
Use this stored procedure to quickly capture all login information when needed, especially for security audit situations.
Managing login and DB user permissions is among the responsibilities that DBAs must take care of. To catch the permission of all Users in a database or all permissions of a certain login on all DBs, down to DB Object levels using SSMS is time consuming, tedious, even impossible, let alone instances with hundreds of databases. Especially, impersonated logins, which show no access on any DB, but they actually have access to hundreds DB objects like tables, view, Stored Procedures in several DBs in the instance. Use this stored procedure to quickly capture all login information when needed, especially for security audit situations.
What it does: This stored Procedure is to skim all login/DB Users in a SQL Server instance along with their DB permissions down to the DB object level. You can store the result set in a table and query the table to find out:
- All Database User accounts and all permission granted on a specific DB.
- Permissions of a specific DB User on a specific DB.
- All permissions that a certain login account possesses on all DBs in the instances.
Author: Stephanie Nguyen
CREATE proc [dbo].[DBASP_ALL_UserPermission]
AS
DECLARE @strSQL nvarchar(2000),
@dbname nvarchar(256)
IF OBJECT_ID('tempdb..#DBUsers') IS NOT NULL DROP TABLE #DBUsers
CREATE table #DBUsers
(
DBname varchar (256),
LoginName varchar(100),
DBUserName varchar(100),
[DBRole] varchar (100),
PrincipalType varchar(100),
PermissionName varchar(100) ,
ObjectType varchar(50),
Objectname varchar(100),
Columnname varchar(100)
)
DECLARE listdbs Cursor
FOR
SELECT name from master.dbo.sysdatabases
WHERE name not in ('master', 'model', 'msdb', 'tempdb')
OPEN listdbs
FETCH next
FROM listdbs into @dbname
WHILE @@fetch_status = 0
BEGIN
SELECT @strSQL =
'
Use ['+ @dbname+'] ;
SELECT
DB_name()
,sp.name
,dp.name
,dp2.name
,dp.type_desc
,perm.permission_name
, objectType = case perm.class
WHEN 1 THEN obj.type_desc
ELSE perm.class_desc
END
,objectName = case perm.class
when 1 then Object_name(perm.major_id)
when 3 then schem.name
when 4 then imp.name
END
, col.name
FROM
sys.database_role_members drm
RIGHT JOIN sys.database_principals dp
on dp.principal_id = drm.member_principal_id
LEFT JOIN sys.database_principals dp2
on dp2.principal_id = drm.role_principal_id
FULL JOIN sys.server_principals sp
ON dp.[sid] = sp.[sid]
LEFT JOIN sys.database_permissions perm
ON perm.[grantee_principal_id] = dp.[principal_id]
LEFT JOIN sys.columns col
ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.objects obj
ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas schem
ON schem.[schema_id] = perm.[major_id]
LEFT JOIN sys.database_principals imp
ON imp.[principal_id] = perm.[major_id]
WHERE dp.name not in (''sys'' , ''information_schema'' , ''guest'', ''public'')
ORDER by sp.name
'
INSERT into #DBUsers
EXEC (@strSQL)
FETCH NEXT
FROM listdbs into @dbname
END
CLOSE listdbs
DEALLOCATE listdbs
SELECT * from #DBUsers
GO
Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued
Back to Database Journal Home