T-SQL:
Declare @databaseprincial varchar(150) = ''
Declare @objectname varchar(150) = 'City'
Declare @objecttype varchar(150) = 'U'
SELECT
obj.[Name] as 'Object Name',
obj.[type] as 'Object Type',
obj.[type_desc] as 'Object Type Description',
dp.Permission_Name,
dp.State_Desc,
dpr.[Name] as 'Database Principal',
dpr.[Type_Desc] as 'Database Principal Type',
obj.Create_Date,
obj.Modify_Date,
obj.Is_Published
FROM sys.objects obj
INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id
INNER JOIN sys.database_principals dpr on dp.grantee_principal_id = dpr.principal_id
WHERE
(@databaseprincial = '' or dpr.[Name] = @databaseprincial)
AND (@objectname = '' or obj.[Name] IN (SELECT value FROM STRING_SPLIT(@objectname, ','))) --Use for specific object search
--AND (@objectname = '' or obj.[Name] Like '%' + (SELECT value FROM STRING_SPLIT(@objectname, ',')) + '%') --Use if want to search for % objectname %
AND (@objecttype = '' or obj.[type] = @objecttype)
AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT
--AND dp.permission_name = 'EXECUTE' --DELETE, INSERT, SELECT, UPDATE, EXECUTE
order by obj.[name] asc, dpr.[Name] asc
|
|
|
|
|
|