Internet Information Server (IIS) – Application Pool – Tracking – Day 2

Background

This is the second post on our series on tracking the status of IIS’s Application Pool.

Lineage

Here is our initial post:

  1. Internet Information Server (IIS) – Application Pool – Tracking
    Link

 

TroubleShooting

Event Viewer

Log Parser Studio

Queries

Query – Get All WAS Entries
Query
SELECT TOP 1000

         TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') as TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Message
  
from  '[LOGFILEPATH]'

where ( SourceName = 'WAS' ) 
 
ORDER BY
           ComputerName
         , TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') DESC

Output

 

Query – Get WAS Entries – Application Pool Disabled
Query


SELECT TOP 1000

         TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') as TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Message
  
from  '[LOGFILEPATH]'

where ( SourceName = 'WAS' ) 
 
and ( Message like '%disable%' ) 

ORDER BY
           ComputerName
         , TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') DESC
Output

 

Summary

There are a few entries bearing the Source WAS in Windows System Event Viewer.
Inclusive are :

  1. A process serving application pool ‘DefaultAppPool’ failed to respond to a ping. The process id was ‘6208’.
  2. A process serving application pool ‘DefaultAppPool’ suffered a fatal communication error with the Windows Process Activation Service. The process id was ‘13844’. The data field contains the error number.
  3. A worker process with process id of ‘21412’ serving application pool ‘DefaultAppPool’ has requested a recycle because the worker process reached its allowed processing time limit.
  4. Application pool ‘DefaultAppPool’ is being automatically disabled due to a series of failures in the process(es) serving that application pool.

 

The ones most pernicious is “Application pool ‘DefaultAppPool’ is being automatically disabled due to a series of failures in the process(es) serving that application pool. “

Internet Information (IIS) / Log Parser – Queries – String Pattern Matching

Background

Looking for File I/O Exceptions in the Event Viewer.

 

Query

Sample

Sample 001

Code


SELECT TOP 100 
 
         TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Mesg
       , Strings as Strings
       , EXTRACT_TOKEN(Strings,1,'|') AS AppName
       , EXTRACT_TOKEN(Strings,2,'|') AS AppVersion
       , EXTRACT_TOKEN(Strings,3,'|') AS S3
       , EXTRACT_TOKEN(Strings,4,'|') AS Module
       , INDEX_OF(Message, 'System.IO.IOException') as indexOf
       , case INDEX_OF(Message, 'System.IO.IOException') 
            when 0 then 'N'
            when NULL then 'N'
            else 'Y'
         end as IOE
       , CASE strcnt(Message, 'System.IO.IOException')
             when 0 then 'No'
             else 'Yes'   
         end as IOException
 
from  '[LOGFILEPATH]'
 
WHERE ( EventType = 1 OR EventType = 2 )

and    INDEX_OF(Message, 'System.IO.IOException') > 0

 
ORDER BY
         TimeGenerated DESC


Output

 

Explanation

  1. INDEX_OF
    • We use INDEX_OF to find the position of the sought string in the Message column
      • When the column contains System.IO.IOException the query returns the starting position of the found pattern
      • When not found, null is returned
  2. STRCNT
    • We invoke STRCNT to count number of matches
      • When String not found, 0 return
      • When matched, number of matches

 

References

  1. StackOverflow
    • Log Parser Case Statement
      Link

 

Internet Information Server (IIS) – Application Pool – Tracking

Background

We have a website that has been going offline.

TroubleShooting

MS Windows

Event Viewer

We started looking at the Event Viewer.

Windows Logs – System

Events

Here is what we see in the Event Viewer:

 

Source – WAS, Event ID = 5011

General
Image

 

Textual

A process serving application pool ‘DefaultAppPool’ suffered a fatal communication error with the Windows Process Activation Service.
The process id was ‘1720’. The data field contains the error number.

Details
Image

 

References

  1. IIS Windows Process Activation Service (WAS) > IIS Application Pool > IIS Application Pool Availability
    • Event ID 5011 — IIS Application Pool Availability
      Link

Summary

It looks like something is knocking our Application\Application Pool down.

 

 

Windows – Event Viewer Parsing Through Log Parser Studio

Background

Need to parse MS Windows Event Logs.

One of the ways to do so is to use Log Parser Studio.

 

Event Viewer

Let us save the events unto the File System.

Outline

  1. Launch Event Viewer
  2. Select the Logs you want ( Application / System / Security )
  3. Right click on the Logs and from the drop down menu, choose “Save All Events As …
  4. Choose Folder And Filename
  5. The file is saved with an extension of “Event Files (*.evtx )

 

Images

Launch Save Event As

Choose Filename

 

Log Parser Studio

Outline

  1. Launch Log Parser Studio
  2. Choose Log Type: EVTLOG
  3. Enter Query
  4. Execute Query

 

Choose Log Type : EVTLOG

Sample Queries


/*  Find top 1000 warnings and errors in the Application Log 
    Levels: 1=Error, 2=Warning                                
*/
SELECT TOP 1000 
             TimeGenerated
           , ComputerName
           , EventCategoryName
           , EventTypeName
           , EventID
           , SourceName
           , Message
FROM 'C:\Temp\04_WindowsLogs_Applications_20170518_0403PM.evtx'
WHERE ( EventType = 1 OR EventType = 2 )
AND   (
               (SourceName like 'ASP%' )
            or (SourceName = '.NET Runtime' )
            or (SourceName = 'Application Error' )
      )
ORDER BY TimeGenerated DESC


Click Execute Button

Click on the Execute Button – The Read icon with the exclamation mark!

 

Sample Output

 

Export

Outline

  1. In Log Parser Studio, use menu File \ Export \ Output as .CSV
  2. In the “Choose Location to save CSV File” window, please specify folder and file name

 

Images

File \ Export \ “Output as .CSV”

 

Choose Location to save CSV File

Excel File

 

SQL Server – Reporting Services ( v2005 ) on Windows 2008 – Web Service Setting

Error

Reporting Services Configuration Manager – Configure Report Server – Web Services Identity – Red Exclamation Mark

WebServiceIdentity-Red (Cropped)

 

Reporting Services Configuration Manager – Configure Report Server – Web Services Identity – Set to new values

WebServiceIdentity-Setting-v2 ( Cropped )

Once Apply is clicked on…

ThereWasErrorSettingTheIdentity

 

Tell me more

Image

800708AC

Textual

 ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 800708AC at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool) 

Remediation

Local Groups

Verified that the required local OS Groups are created and that Web Service Account has been added to the designated groups.

The OS Groups are

  1. SQLServer2005ReportingServicesWebServiceUser$<computer-name>$<instance-name>
  2. SQLServer2005ReportServerUser$<computer-name>$<instance-name>
  3. IIS_WPG

 

Group Intent Naming Convention Sample
 WebServiceUsers Members in the group have the required access to be assigned as the log on account for the associated instance of SQL Server Reporting Services in SQL Server 2005  SQLServer2005ReportingServicesWebServiceUser$<computer-name>$<instance-name>  SQLServer2005ReportingServicesWebServiceUser$HRDB$V2005MIRROR
 ReportServerUsers Members in the group have the required access and privileges to be assigned as the log on account for the associated instance of SQL Server Reporting Services in SQL Server 2005. SQLServer2005ReportServerUser$<computer-name>$V2005MIRROR  SQLServer2005ReportServerUser$HRDB$V2005MIRROR
 IIS_WPG  The IIS_WPG user is a the IIS Worker Process Group, and is needed in order for IIS to run / access applications within a folder.

The IIS_IUSRS is a the Internet Guest Account, this is used to allow anonymous access to the folder / files

 

 

WebServiceUsers

Original

WebServiceUsers-20160726-1055PM ( Original)

 

Revised

WebServiceUsers-20160726-1058PM ( Revised )

 

ReportServerUsers

ReportServerUsers-20160726-1053PM ( Original)

 

IIS_WPG

  1. Created a new group ( IIS_WPG)
  2. Added Web Service Account to group ( IIS_WPG)

IIS_WPG ( Created group & added Web Service)

Web Service Identity – Successfully Changed

WebServiceIdentity-SuccessfullyChanged ( Cropped)

 

Crediting

Crediting Dave Sells (MSFT ).

Answer provided

SQL Server > SQL Server Reporting Services, Power View
Set Web Service Identity to domain account
Here

…\Reporting Services\ReportServer\rsreportserver.config

WebServiceAccount is empty

Is WebServiceAccount Element is empty?

Here we have the WebServiceAccount Element marked as empty

WebServiceAccountIsEmpty

 

Please fill it with the account name, can be local account or domain name.

WebServiceAccountFilled

Causation

A mangled installation \ configuration can lead to the WebServiceAccount not being properly recorded in the rsreportserver.config.

 

Crediting

Crediting JoeUMN ( Profile ).

WebServiceAccountAsEmpty

Answer provided

SQL Server > SQL Server Reporting Services, Power View
Set Web Service Identity to domain account
Here

Virtual Directory Application Pool set incorrectly

There are two virtual directories ( Reports and ReportServer), either of them could be set incorrectly

To correct do the following:

  1. Launch Internet Information Services ( IIS ) Manager
    • Access the corresponding Web site and Virtual Directory ( by default “Default Web Site” \ “ReportServer” and “Default Web Site” \ “Reports” )
    • Right click on your selection
    • From the drop-down menu select “Manage Application” \ “Advanced Settings…”
    • The “Advanced Settings” window
      • In the “Application Pool” dropdown, review the Application Pool selected
        • In our case a former one was selected and so we changed to the new one

 

Access Website \ Virtual Directory – Application \ Manage Application \ Advanced Settings

LaunchApplicationAdvancedSettings

 

Website \ Virtual Directory – Application \ Manage Application \ Advanced Settings { Incorrect }

ApplicationAdvancedSettings-InCorrected

 

Website \ Virtual Directory – Application \ Manage Application \ Advanced Settings { Transitioning }

 

SelectApplicationPool

Website \ Virtual Directory – Application \ Manage Application \ Advanced Settings { Corrected }

ApplicationAdvancedSettings-Corrected

 

Crediting

Crediting JoeUMN ( Profile ).

ChangeApplicationPoolOnMsdn

Answer provided

SQL Server > SQL Server Reporting Services, Power View
Set Web Service Identity to domain account
Here

Summary

Our problem is that SQL Server v2005 targets MS Windows 2003, which has IIS_WPG.

MS Windows 2008 forsook IIS_WPG for IIS_IUSRS.

 

SQL Server – Reporting Services ( v2005 ) on Windows 2008 – Application Pool Settings

Background

A quick and very brief catalog of possible problems one might encounter when configuring SQL Server Reporting Services (v2005) on MS Windows 2008/MS Windows 2008-R2.

Reporting Services COnfiguration on IIS is a broad topic and for the sake of brevity, we restrict our lenses mostly on the Application Pool.

Configuration

Application Pool

  1. .Net Framework version
    • .Net Framework v2.0.50727
  2. Managed pipeline mode
    • Use
      • Classic
    • Do not use
      • Integrated

Screen Shot

EditApplicationPool

 

Error

HTTP Error 500.23 – Internal Server Error – An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

 

Screen Shot

HTTP Error 500DOT23

Tabulated

Item Value
 Module ConfigurationValidationModule
 Handler  AboMapperCustom-7284591
 Error Code  0x80070032
 Logon Method  Not yet determined
 Logon User  Not yet determined

 

Remedy

Alternatively, switch the application to a Classic mode application pool – for example, appcmd set app “Default Web Site/” /applicationPool:”Classic .NET AppPool”. Only do this if you are unable to migrate your application.

 

Application Pool – View Settings

Sample


set "_appPool=AppPoolDomainServiceAccount"
%systemroot%\system32\inetsrv\APPCMD list apppool "%_appPool%" /text:*

Output

Apppool-configuration-view

 

Application Pool – Change Setting – Managed Pipeline Mode

Change Managed Pipeline Mode from Integrated to Classic.

Sample


set "_appPool=AppPoolDomainServiceAccount"
%systemroot%\system32\inetsrv\APPCMD set apppool "%_appPool%" /managedPipelineMode:Classic

Output

Apppool-configuration-set

 

 

Error – Could not find permission set named ‘ASP.Net’.

Screen Shot

CouldNotFindPermissionSetNamedASPNet

 

Textual

[ConfigurationErrorsException: Could not find permission set named 'ASP.Net'.]
   System.Web.HttpRuntime.HostingInit(HostingEnvironmentFlags hostingFlags, PolicyLevel policyLevel, Exception appDomainCreationException) +361

[HttpException (0x80004005): Could not find permission set named 'ASP.Net'.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +579
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +112
   System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +1083

Tabulated

Item Value
ConfigurationErrorsException [ConfigurationErrorsException: Could not find permission set named ‘ASP.Net’.]
HTTP Exception HttpException (0x80004005): Could not find permission set named ‘ASP.Net’.]

 

Code

Application Pool – Change Setting – Managed Runtime Version

Change Managed Runtime Version to 2.0

Sample

set "_appPool=AppPoolDomainServiceAccount"
%systemroot%\system32\inetsrv\APPCMD set apppool "%_appPool%" /managedRuntimeVersion:v2.0
Output

managedRuntimeVersionChangeCommandOutput

Error – Server unavailable

Screen Shot

ServerUnavailable

Textual

Service Unavailable

Service Unavailable

 

HTTP Error 503. The service is unavailable.

Application Pool – Start

Start application pool

Sample
set "_appPool=AppPoolDomainServiceAccount"
%systemroot%\system32\inetsrv\APPCMD start apppool /apppool.name:"%_appPool%"

Output
appPoolStartOutput

Error – Illegal operation attempted on a registry key that has been marked for deletion. (0x800703fa)

Screen Shot

 

Textual

Remediation

The recommended remediation step is to launch Group Policy and

Computer Configuration->Administrative Templates->System-> UserProfiles -> Do not forcefully unload the user registry at user logoff.

Application Pool – Load User Profile – Current Setting

What is the Application Pools – Load User Profile – Current setting?

Sample
set "_appPool=AppPoolDomainServiceAccount"
%systemroot%\system32\inetsrv\APPCMD list apppool "%_appPool%" /text:* | find "loadUserProfile"

Output

viewCurrentSetting-LoadUserProfile

Application Pool – Load User Profile – Set to true

Set Application Pools – Load User Profile to True

 

Sample
set "_appPool=AppPoolDomainServiceAccount"
%systemroot%\system32\inetsrv\APPCMD set apppool "%_appPool%" /processModel.loadUserProfile:true

Output

setLoadUserProfile

Win OS – Desktop

Access Control Panel \ User Accounts and review the list of users that have current profiles.

UserProfiles

 

Error – System.UnauthorizedAccessException.Access to the path … RSReportServer.config is denied

ScreenShot

eventViewer-WindowsLog-Application

 

Textual

 


Service cannot be started. Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ---> System.UnauthorizedAccessException: Access to the path 'D:\Program Files\Microsoft SQL Server\v2005Mirror\MSSQL.4\Reporting Services\ReportServer\RSReportServer.config' is denied. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at Microsoft.ReportingServices.Diagnostics.Globals.Safe...

 

System.UnauthorizedAccessException: Access to the path ‘D:\Program Files\Microsoft SQL Server\v2005Mirror\MSSQL.4\Reporting Services\ReportServer\RSReportServer.config’ is denied

Remedy

Launch “Reporting Services Configuration Manager” and set\reset Windows Service Identity to your designated Service Account.

WindowsServerIdentity (Cropped)

 

References

IIS

Appcmd

  1. Getting Started with AppCmd.exe – By Mike VolodarskyNovember 16, 2007
    Link
  2. Start or Stop an Application Pool (IIS 7)
    Link

 

Application Pool – Error – “ILLEGAL OPERATION ATTEMPTED ON A REGISTRY KEY THAT HAS BEEN MARKED FOR DELETION”

  1. A COM+ application may stop working on Windows Server 2008 when the identity user logs off
    Link
  2. Ned Pyle (MSFT)User Profile Policies in Windows Server 2008 and Windows Vista
    Link
  3. RESOLVING ERROR “ILLEGAL OPERATION ATTEMPTED ON A REGISTRY KEY THAT HAS BEEN MARKED FOR DELETION.”
    Link
  4. Veritas – Events 6287, 45569, 3280, 3263, 6979, 7082 – Illegal operation attempted on a registry key that has been marked for deletion. (0x800703fa)
    Link
  5. thycotic – RESOLVING ERROR “ILLEGAL OPERATION ATTEMPTED ON A REGISTRY KEY THAT HAS BEEN MARKED FOR DELETION.”
    Link

 

Application Pool – Load User Profile

  1. IIS 7 Tip # 3 You can now load the user profile of the application pool identity
    Link

 

Transact SQL – Securing Personally Identifiable Information ( PII) Data at the Database Column Level

Background

We are letting more and more people directly query our database.

But, as we have some Personal Identifier Information ( PII) columns we have to be a bit careful.

Those columns are encrypted, but unfortunately we are using the same symmetric keys and those users need access to some of the encrypted columns, but are to be denied access to others.

 

Denying access to some columns

Granting and Revoking Object & Column permissions

Tasks

  1. Grant view definition on symmetric key
  2. Grant control on certificate
  3. Grant select on specific tables
  4. Revoke select on specific columns ( the targeted table list is a subset of tables listed earlier )

Code


/* 
	Encryption Objects
*/
grant View Definition on  SYMMETRIC KEY::[symkeyPII] to [roleUser]
go

grant control on certificate::[certPII] to [roleUser]
go

grant execute on dbo.Salt  to [roleUser]
go

/* 
	Tables
*/
grant select on [dbo].[employee] to [roleUser]
go

grant select on [dbo].[salary] to [roleUser]
go


/* 
	Table -  Columns
*/
revoke select on 
		 OBJECT::[dbo].[employee]
			(
				  [last4ssn]
				, [drvlicense]
			)
		 from [roleUser]
GO  

go

 

 

Review Granted & Denied Permission Sets

Here is a Stored Procedure that lists granted and denied permissions.

Code



use master
go

if object_id('dbo.sp_ListObjectLevelPermissions') is null
begin

	exec('create procedure dbo.sp_ListObjectLevelPermissions as select 1/0 ')

end
go

alter procedure dbo.sp_ListObjectLevelPermissions
(
	@username sysname
)
as

begin

	/*
		Get Object Level Permissions
	*/
	SELECT
			  [UserName]
			= USER_NAME(dppriper.grantee_principal_id)
 
		, [principalType]
			= dppri.type_desc
 
		, [classDesc]
			= dppriper.class_desc
 
		, objectName
			=
			case dppriper.class_desc    
				when 'DATABASE' then db_Name(dppriper.major_id) 
				when 'OBJECT_OR_COLUMN'
					   then Object_Schema_Name(dppriper.major_id) 
									  + '.'
									  + object_Name(dppriper.major_id) 
				when 'SCHEMA' then schema_Name(dppriper.major_id) 
				when 'SYMMETRIC_KEYS' then
				   ( 
						select [name] 
						from sys.symmetric_keys 
						where dppriper.major_id
							   = symmetric_key_id
					)
					when 'CERTIFICATE' then
					(
						select [name] 
						from sys.certificates 
						where dppriper.major_id = certificate_id
					)
			end


		, columnName
			=
			case dppriper.class_desc    
				when 'OBJECT_OR_COLUMN'
					   then tblSC.name
				else NULL
			end
 
		, [permissionName]
			= dppriper.permission_name
 
		, [permissionState]
			= dppriper.state_desc

		, [grantStatement]
			=
			case dppriper.class_desc    

				when 'DATABASE' 
					then
							  dppriper.state_desc + ' '
							+ dppriper.permission_name
							+ case 

								when dppriper.[state] in ('G', 'W')
									 then ' to '

								when dppriper.[state] in ('D', 'R')
										 then ' from '

							  end
							+ quotename(USER_NAME(dppriper.grantee_principal_id))
							+ ';'
								collate database_default


				when 'OBJECT_OR_COLUMN'
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ quotename(Object_Schema_Name(dppriper.major_id)) 
								+ '.'
								+ quotename(object_Name(dppriper.major_id) )
								+ ' '
								+
									case
										when ( dppriper.minor_id is null ) then ''
										when ( dppriper.minor_id = 0 ) then ''
										else '('
												+ 
													case dppriper.class_desc    
															when 'OBJECT_OR_COLUMN'
																then quotename(tblSC.name)
														 else ''
													end
											 + ')'
									end

								+ case 

									when dppriper.[state] in ('G', 'W')
										 then ' to '

									when dppriper.[state] in ('D', 'R')
											 then ' from '

									else 'N/A'

								  end

								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'SCHEMA' 
						then
								  dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' SCHEMA :: '
								+ quotename(Schema_Name(dppriper.major_id)) 
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'SYMMETRIC_KEYS' 
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' symmetric key::'
								+
								   ( 
										select quotename( [name] )
										from sys.symmetric_keys 
										where dppriper.major_id
											   = symmetric_key_id
									)
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default

				when 'CERTIFICATE' 
					   then dppriper.state_desc + ' '
								+ dppriper.permission_name
								+ ' on '
								+ ' certificate::'
								+
								   ( 
										select quotename( [name] )
										from sys.[certificates] tblSC
										where dppriper.major_id
											   = certificate_id
									)
								+ ' to '
								+ quotename(USER_NAME(dppriper.grantee_principal_id))
								+ ';'
								 collate database_default



			end

			--, dppriper.major_id			
 
	FROM    sys.database_permissions dppriper
 
	INNER JOIN sys.database_principals dppri
 
		   ON dppriper.grantee_principal_id = dppri.principal_id

	LEFT OUTER JOIN sys.columns tblSC
 
		    ON dppriper.major_id = tblSC.object_id
		   AND dppriper.minor_id = tblSC.column_id

 
	where dppriper.grantee_principal_id 
		= case
				 when @username is null then dppriper.grantee_principal_id
			 
				 else user_id(@username)
 
			  end

	UNION ALL

	/*
		Get Role Permissions
	*/
	SELECT
			  [UserName]
			= USER_NAME(dppriper.member_principal_id)
 
		, [principalType]
			--= dppri.type_desc
 			= 'Database Role'

		, [classDesc]
			--= dppriper.class_desc
			= 'Database Role'
 
		, objectName
			= 'Database' collate database_default

		, columnName
			= null

		, [permissionName]
			= cast(dppriR.name as sysname) collate database_default

			 
		, [permissionState]
			= 'Grant' collate database_default

		, [grantStatement]
			= 'exec sp_addrolemember '
					+ '  @rolename = '
					+ quotename(dppriR.name)
					+ ' '
					+ ' ,'
					+ '  @membername = '
					+ quotename(USER_NAME(dppriper.member_principal_id))
					+ ' '
					+ ';'
				 collate database_default

 
    FROM	sys.database_role_members dppriper
 
	INNER JOIN sys.database_principals dppriM
 
		   ON dppriper.member_principal_id = dppriM.principal_id

	INNER JOIN sys.database_principals dppriR
 
		   ON dppriper.role_principal_id = dppriR.principal_id

	where dppriper.member_principal_id 
		= case
				 when @username is null then dppriper.member_principal_id
			 
				 else user_id(@username)
 
		  end


	order by
		  USERNAME
		, [principalType]
		, objectName
		, permissionName


end

go

grant execute on [dbo].[sp_ListObjectLevelPermissions] to [public]
go

EXEC sys.sp_MS_marksystemobject 'dbo.sp_ListObjectLevelPermissions'
go



Review Permissions

Code


use [HRDB]
go

declare @username sysname

set @username = 'roleUser'

exec dbo.sp_ListObjectLevelPermissions
 @username = @username

Output

permissionSet

Source Control

GitHub

Link