NuRoN Consulting, INC.

Blog > SQL Tools > Databases > master > Procedures > sp_GrantAll

Dependencies for System Stored Procedure: master.dbo.sp_GrantAll

ReferencingFQN	ReferencingType	ReferencedFQN	ReferencedType	Depth	RedHierarchy
master.dbo.sp_GrantAll	Stored procedure	Util.dbo.PrintLargeText	Stored procedure	1	[Util].[dbo].[PrintLargeText]
master.dbo.sp_GrantAll	Stored procedure	Util.dbo.StringConcat	Aggregate function (CLR)	1	[Util].[dbo].[StringConcat]
Util.dbo.PrintLargeText	Stored procedure	Util.dbo.PrintLargeText_CLR	Assembly (CLR) stored-procedure	2	[Util].[dbo].[PrintLargeText], [Util].[dbo].[PrintLargeText_CLR]
Util.dbo.StringConcat	Aggregate function (CLR)	Util.UtilClr	ASSEMBLY	2	[Util].[dbo].[StringConcat], [Util].[UtilClr]

System Stored Procedure: master.dbo.sp_GrantAll

USE master
GO
IF OBJECT_ID('sp_GrantAll') IS NULL EXEC ('CREATE PROCEDURE sp_GrantAll AS SELECT 1 AS ID')
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

Description for System Stored Procedure: master.dbo.sp_GrantAll

Todo