In today’s universe of multi-tiered distributed processing, VMs, etc. you may need a quick and easy way to generate and migrate T-SQL scripts capable of setting up the necessary permissions so that an enterprise service account will be able to execute them.
In this example, I am looking for all NON-Microsoft-Shipped (IsMSShipped) stored procedure (sproc) objects within a database instance. Once I find them, I dub the result set with the text necessary to execute the output (e.g. GRANT EXECUTE ON) as a separate T-SQL script.
I generally run this sproc interactively in SQL Manager, then right-click on the output box to cut and paste the results into another query window for the db instance I want to execute the newly generated t-sql code in.
This sproc has a PRINT option–and an EXECUTE option. The PRINT option will generate the t-sql statements to the output window WITHOUT executing the statement. The EXECUTE option will print the statement first, then execute it. Execute the sproc with the PRINT option first to be sure that the subset of sprocs selected is the subset you are interested in assigning grants.
example:
exec GrantSprocsExecForWinSvcAcct ‘PRINT’
————— Beginning of Stored Proc ——————-
USE [Metro]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: plditallo@metro-design-dev.com
– Create date: 6/11/2009
– Description: looks for stored proc objects, outputs grant
– statements for future t-sql execution.
– =============================================
ALTER PROCEDURE [evaluate].[GrantSprocsExecForWinSvcAcct]
(
@UserName varchar(100),
@Which varchar(5)
)
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME
INTO #Procedurelist
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),’IsMSShipped’) =0
AND ROUTINE_TYPE=’PROCEDURE’
AND SPECIFIC_NAME NOT LIKE ‘sp_%’
AND SPECIFIC_NAME NOT LIKE ‘xp_%’
ORDER BY SPECIFIC_NAME
DECLARE
@Loopid INT,
@MaxId INT
– @UserName VARCHAR(50)
– This is the user that will get the execute permissions
– // used as a test
– SELECT @UserName = ‘Metro\svc$WinAcct-dev’
– Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(ID)
FROM #Procedurelist
DECLARE
@SQL VARCHAR(500),
@ProcName VARCHAR(400)
PRINT ‘Beginning process…’
– This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN
– get the procedure name
SELECT @ProcName = SPECIFIC_NAME
FROM #Procedurelist
WHERE ID = @Loopid
IF(upper(@Which) = 'EXEC')
BEGIN
– construct the statement
SELECT @SQL = 'GRANT EXECUTE ON ' + @ProcName + ' TO ' + @UserName
PRINT (@SQL)
EXECUTE (@SQL)
END
IF(upper(@Which) = 'PRINT')
BEGIN
– construct the statement
SELECT @SQL = 'GRANT EXECUTE ON ' + @ProcName + ' TO ' + @UserName
PRINT (@SQL)
END
– increment counter
SET @Loopid = @Loopid + 1
END
– clean up
DROP TABLE #Procedurelist
PRINT 'End of processing. If you chose EXEC, please spot check stored procedures to assure execute permission has been granted to user.'
END
