USE [master]
GO
IF OBJECT_ID('dbo.sp_GrantAll') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_GrantAll AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.sp_GrantAll
@PrintSQL BIT = 0,
@ExecSQL BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON ;
WITH p AS (SELECT name AS ProcName
FROM master.sys.procedures (NOLOCK)
WHERE name LIKE 'sp[_]%'
AND OBJECT_ID <> @@PROCID
AND TYPE = 'P'
AND SCHEMA_ID = 1),
g AS (SELECT NAME AS Principal
FROM master.dbo.syslogins
WHERE isntgroup = 1
OR isntuser = 1
AND sysadmin = 0
AND (name NOT LIKE 'NT %'
AND NAME NOT LIKE 'BUILT%'))
SELECT @SQL = 'USE master
' + Util.dbo.StringConcat('GRANT EXECUTE ON [dbo].[' + ProcName + '] TO [' + Principal + ']
GRANT VIEW DEFINITION ON [dbo].[' + ProcName + '] TO [' + Principal + ']
', '') + Util.dbo.StringConcat(DISTINCT 'GRANT SELECT ON FN_TRACE_GETTABLE TO [' + Principal + '];
GRANT VIEW SERVER STATE TO [' + Principal + '];
GRANT SHOWPLAN TO [' + Principal + '];
GRANT ALTER TRACE TO [' + Principal + '];
', '')
FROM p
CROSS JOIN g
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
AND @SQL <> ''
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject
sp_GrantAll
GO