If you are wondering what objects were used to insert some records in a table … (today was a case , when there were some records , but don’t know what stored object was used …)
;WITH RoleMembers (member_principal_id, role_principal_id) AS
(SELECT
rm1.member_principal_id,
rm1.role_principal_id
FROM sys.database_role_members rm1 (NOLOCK)
UNION ALL
SELECT
d.member_principal_id,
rm.role_principal_id
FROM sys.database_role_members rm (NOLOCK)
INNER JOIN RoleMembers AS d
ON rm.member_principal_id = d.role_principal_id
)
–SELECT * FROM RoleMembers where
SELECT DISTINCT
S.[name] + N’.’ + O.[name] AS ObjName,
SM.[definition]
,STUFF(dp.lstUsers,1,1,”) as lstUsers
FROM sys.sql_modules AS SM
INNER JOIN sys.objects AS O
ON SM.[object_id] = O.[object_id]
INNER JOIN sys.schemas AS S
ON O.[schema_id] = S.[schema_id]
OUTER APPLY
(SELECT ‘,’+ u.name
FROM sys.database_permissions as dp
INNER JOIN sys.database_principals AS pr
ON dp.grantee_principal_id = pr.principal_id
INNER JOIN RoleMembers as r
ON r.role_principal_id = pr.principal_id
INNER JOIN sysusers U ON U.uid = r.member_principal_id
WHERE
dp.major_id = o.object_id
FOR XML PATH(”)
)dp(lstUsers)
WHERE SM.[definition] LIKE ‘%tblXXX%’
and SM.[definition] LIKE ‘%Insert%’
and SM.[definition] LIKE ‘%keyText1%’
and SM.[definition] LIKE ‘%keyText2%’
and SM.[definition] LIKE ‘%keyText3%’
and SM.[definition] LIKE ‘%keyText4%’
and SM.[definition] NOT LIKE ‘%afield%’
and dp.lstUsers like ‘%aUserName%’
ORDER BY 1 asc
So, I’m searching , in the body of a object (sys.sql_module) , for the presence :
– of a table (tblXXX)
– to have the key word “insert”
– additional keys/word keyText1
– not having the missing field/word afield
– and particular user have rights on this object : and dp.lstUsers like ‘%aUserName%’
S
Sql Server 