Posted on

Generate All Grants in a Database

Use this handy SQL to generate all grants that exist in the current database.

SELECT
 (
 dp.state_desc + ' ' +
 dp.permission_name collate latin1_general_cs_as + 
 ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
 ' TO ' + '[' + dpr.name + ']'
 ) AS GRANT_STMT
FROM sys.database_permissions AS dp
 INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
 INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
 INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE dpr.name NOT IN ('guest')
Posted on

Find Agent Jobs Containing a String

Use this handy query to find all agent jobs that have steps containing a search string.

DECLARE @Search varchar(255)
SET @Search='usp_YourStoredProc'
 
SELECT j.name, j.enabled, s.step_id, s.database_name, s.subsystem, s.command
FROM msdb.dbo.sysjobsteps s
INNER JOIN msdb.dbo.sysjobs j ON s.job_id = j.job_id
WHERE command LIKE '%'+@Search+'%'
ORDER BY 1, 3

Thanks to this StackOverflow post for pointing me in the right direction.