Blog Pages

List all modules where an "execute as" has been specified

select object_name(object_id) from sys.sql_modules where execute_as_principal_id = user_id('Lms')
GO

SELECT  objects.type_desc                       AS ObjectTypeDescription
,               objects.type                            AS ObjectTypeCd
,               SCHEMA_NAME(objects.SCHEMA_ID) AS SchemaName
,               objects.name                            AS ObjectName
,               USER_NAME(sql_modules.execute_as_principal_id) ExecAsUserName
,               objects.SCHEMA_ID
,               sql_modules.execute_as_principal_id
FROM    sys.objects                                     AS objects
JOIN    sys.sql_modules                         AS sql_modules
                ON sql_modules.OBJECT_ID        = objects.OBJECT_ID 
WHERE   sql_modules.execute_as_principal_id  IS NOT NULL -- Exec As is not "owner"
--AND             sql_modules.execute_as_principal_id <> COALESCE(objects.principal_id , objects.SCHEMA_ID )
GO

No comments:

Post a Comment