Useful Script

Here is a SQL script that grants execute access to all user stored procedures and functions. While I didn't write this particular script, it comes in handy after a restore!

IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'GrantExecOnStoredProc')
DROP PROCEDURE dbo.GrantExecOnStoredProc
GO
CREATE PROCEDURE dbo.GrantExecOnStoredProc @SQLUserName SYSNAME AS
SET NOCOUNT ON
DECLARE @cmd VARCHAR(200)
CREATE TABLE #tmp (cmd VARCHAR(100))
INSERT INTO #tmp
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO ' + @SQLUserName
FROM sysobjects WHERE XTYPE = 'P' OR XTYPE = 'FN'
WHILE (EXISTS (SELECT * FROM #tmp))
BEGIN
SELECT TOP 1 @cmd = cmd FROM #tmp
EXEC (@cmd)
DELETE FROM #tmp WHERE cmd = @cmd
END
DROP TABLE #tmp
SET NOCOUNT OFF
GO

EXEC GrantExecOnStoredProc '[SQL\AD Account]'