October 20, 2010 at 2:13 am
Hi,
Can any one out there,
Please advise me the alternate permissions other than the SYSADMIN to grant the user to debug all sql stored proceudres in an instance.
regards,
GLNP
October 20, 2010 at 7:30 am
here is a good way... I didn't create the proc...but you can make the needed changes to it....like change the grant exec to grant alter.....
if you are using window auth and you have a group, then all you need to do is run the script for one group other wise you will need to run multiple times for each person
USE database
GO
/****** Object: StoredProcedure [dbo].[spGrantExectoAllStoredProcs] Script Date: 07/30/2009 15:46:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGrantExectoAllStoredProcs] @user sysname
AS
/*----------------------------------------------------------------------------
-- Object Name: spGrantExectoAllStoredProcs
-- Author: Edgewood Solutions
-- Development Date: 03.19.2007
-- Called By: TBD
-- Description: Issue GRANT EXEC statement for all stored procedures
-- based on the user name that is passed in to this stored procedure
-- Project: SQL Server Security
-- Database: User defined databases
-- Business Process: SQL Server Security
--
----------------------------------------------------------------------------
-- Num | CRF ID | Date Modified | Developer | Description
----------------------------------------------------------------------------
-- 001 | N\A | 03.15.2007 | Edgewood | Original code for the GRANT
-- EXEC process
--
--
*/
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @CMD2 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
-- 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
-- 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'dt_%' and ROUTINE_NAME NOT LIKE 'sp_%'
AND ROUTINE_TYPE = 'PROCEDURE' order by routine_name
-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN
-- 6 - Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID
-- 7 - Build the string
SELECT @CMD1 = 'GRANT VIEW DEFINITION ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user
SELECT @CMD2 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user
-- 8 - Execute the string
-- SELECT @CMD1
EXEC(@CMD1)
EXEC(@CMD2)
-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END
-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
October 20, 2010 at 8:28 am
Please don't post to multiple locations. It just messes up the conversation.
There are also responses over here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply