Bobs Script Vault

Sessions: 239	The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
LifeAsBob		Tuesday, November 26, 2024

lifeasbob.com

Home
Blog
bob tech
Calendar
Contact Bob
Pictures
VW Buses
HomeBrew
Cook Book

vw_logo_with_girl.jpg

bob Links

Web Workshop - Vecto...

Would I were in an alehouse in London. - William Shakespeare, Henry V

Bob Code 4957

Things

Script Vault:

280 scripts loaded.

Script Name Code Description

Script

DROP TABLE IF EXISTS #Permissions

DECLARE @p_userName NVARCHAR(40) = '' -- 'A3PCTXOADF01T'
--'A3PCAPMADF01T'	 -- Search for an individual users permissions - Specify up to 7 characters here (or none for all users)

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserType        : Value will be either 'SQL User', 'Windows User', or 'Windows Group'. This reflects the type of user/group defined for the SQL Server account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the same as the server user.
LoginName       : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
Schema          : Name of the schema the object is in.
ObjectName      : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function.
*/

DECLARE @Public NVARCHAR(6) = ''  -- Use 'PUBLIC' to List all access provisioned to the public role

DECLARE @userName NVARCHAR(42) = '%' + @p_UserName + '%'
print @userName

--List all access provisioned to a sql user or windows user/group directly

SELECT
        [UserType] = CASE princ.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
						 WHEN 'E' THEN 'Azure User'
						 WHEN 'R' THEN 'DATABASE_ROLE'
						 WHEN 'X' THEN 'Azure group'
                     END,
        [DatabaseUserName] = princ.[name],
        --[LoginName]        = ulogin.[name],
        [Role]             = NULL,
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
				into #permissions
    FROM
        --Database user
        sys.database_principals            AS princ
        --Login accounts
        --LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G', 'E', 'X')
        -- No need for these system accounts
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
		AND princ.[name] LIKE @userName -- I just added

UNION

--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
						 WHEN 'E' THEN 'Azure User'
						 WHEN 'R' THEN 'DATABASE_ROLE'
						  WHEN 'X' THEN 'Azure group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        --[LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
       -- LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G', 'E', 'R', 'X')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
		AND membprinc.[name] LIKE @userName  -- I just added

UNION

--3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        --[LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]

FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = @Public
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    --[LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

ALTER TABLE #Permissions  ALTER COLUMN PermissionState nVARCHAR(256) COLLATE Latin1_General_CI_AS  NULL
ALTER TABLE #Permissions  ALTER COLUMN PermissionType nVARCHAR(256) COLLATE Latin1_General_CI_AS  NULL
ALTER TABLE #Permissions  ALTER COLUMN DatabaseUserName nVARCHAR(256) COLLATE Latin1_General_CI_AS  NULL
ALTER TABLE #Permissions  ALTER COLUMN ObjectName nVARCHAR(256) COLLATE Latin1_General_CI_AS  NULL
ALTER TABLE #Permissions  ALTER COLUMN [schema] nVARCHAR(256) COLLATE Latin1_General_CI_AS  NULL
ALTER TABLE #Permissions  ALTER COLUMN [ROLE] nVARCHAR(256) COLLATE Latin1_General_CI_AS  NULL

select UserType,DatabaseUserName,Role,'CreateUser' as PermissionType,PermissionState,ObjectType,[Schema],ObjectName,ColumnName
	,CASE WHEN PermissionType = 'Connect' and DATABASEUSERNAME != 'dbo' THEN
		CASE WHEN [UserType] = 'Azure User' THEN '--Create user [' + DatabaseUserName + '] FROM EXTERNAL PROVIDER;' 
			ELSE '--CREATE USER [' + DatabaseUserName + '] WITH PASSWORD = ' + char(39) + 'New_Password_' + replace(newid(),'-','') + char(39) 
	END END as TSQLCreate
	from #permissions where PermissionType = 'Connect' and DatabaseUserName not in ('dbo','guest')
union all
	select 
	*,
	-- combining the connect and create in one statment works but cut and paste is quircky bcause of crlf, so made seperate sql with union statement
--	CASE WHEN PermissionType = 'Connect' and DATABASEUSERNAME != 'dbo' THEN
--		CASE WHEN [UserType] = 'Azure User' THEN 'Create user [' + DatabaseUserName + '] FROM EXTERNAL PROVIDER;' 
--			ELSE 'CREATE USER [' + DatabaseUserName + '] WITH PASSWORD = {NEWPASSOWRD};' END + char(13) + char(10) + 
--		 PermissionState + ' ' + PermissionType + ' TO [' + DatabaseUserName + ']'
	CASE WHEN PermissionType = 'Connect' and DATABASEUSERNAME != 'dbo' THEN
			 PermissionState + ' ' + PermissionType + ' TO [' + DatabaseUserName + ']'
	ELSE 
	CASE WHEN [Role] is not Null THEN
		'ALTER ROLE ' + [Role] + ' ADD MEMBER [' + DatabaseUserName + ']' 
	ELSE 
	CASE WHEN ObjectType = 'Schema' THEN
		PermissionState + ' ' + PermissionType  + ' ON SCHEMA::' + ObjectName +  ' TO [' + DatabaseUserName + ']' 
	ELSE
	CASE WHEN ObjectType = 'Database' THEN
		PermissionState + ' ' + PermissionType  + ' TO [' + DatabaseUserName + ']' 
	ELSE
	PermissionState + ' ' + PermissionType + ' on ' + [Schema] + '.' + ObjectName + ' TO [' + DatabaseUserName + ']' 
	END END END END as TSQLCreate
	 
	from #permissions

Notes:

Update by:	bob
Updated On:	5/30/2023 9:34:00 AM
Created by:	rhorkay
Created On:	5/7/2019 1:43:00 PM

Script Name:

Think ! The text boxes below only works if your particular SQL Server is available via where this website is hosted / on the internet, so this is really code that is for me !; though I am working to update it to run either server side or client side via a check box, this will be a nice future enhancement (think AJAX).
Integrated Authentication	Time Out: Wrap cell contents in results Display Messages (Print)	Run For a specific database>