Finding a Database role with permission for Objects like (Table/View) to ensure the users got appropriates access permission within the database.
I have written this query to find database role (Users) with Object permissions.
USE [UserDatabase]
go
SELECT
sp.[state_desc] ,
sp.[permission_name],
'ON'as Col1,
ss.[name] AS [Schema_name],
so.[name] as [Table_View_name],
--so.[Type],
'TO' as Col2,
dr.[Name]
FROM sys.objects as so
INNER JOIN sys.schemas as ss ON so.schema_id = ss.schema_id
INNER JOIN sys.database_permissions AS sp ON sp.major_id = so.object_id
INNER JOIN sys.database_principals AS dr ON dr.principal_id = sp.grantee_principal_id
where so.[Type] ='V'
Order by dr.[Name]