Here's a sample ExecuteSQL statement for a policy based management Check Condition. In this scenario, the Facet is set to "Database"; which will cause it to run the check below once for each database on the sql instance. The facet contains an @Name property for DBName. How do a access that from within the ExecuteSQL function? For example purposes, where I have the @Name value in the script that I want to be replaced with the current database name.
ExecuteSql('String',
'SELECT @Name, Us.name AS username, Obj.name AS object, dp.permission_name AS permission
FROM sys.database_permissions dp
JOIN sys.sysusers Us
ON dp.grantee_principal_id = Us.uid
JOIN sys.sysobjects Obj
ON dp.major_id = Obj.id
WHERE US.name = ''public''
'
)
EDIT:
I've seen this link, which seems to pass Sys.Objects and Sys.Schema info, but can't seem to get DBName info.
http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx