My Techie Blog

  Home  |   Contact  |   Syndication    |   Login
  110 Posts | 5 Stories | 406 Comments | 0 Trackbacks

News

Archives

Post Categories

Image Galleries

ADO.NET

Ajax

API/WebServices

ASP.NET Resources

BizTalk Server

Blogs I read

Free Books

How to

iOT

JavaScript

JQuery

JSLink - SharePoint

JSON

LINQ

Misc

Mobile BI

MS Business Inteliigence

Reporting Services

Responsive/MVC ASP.NET

SEO

Sharepoint Resources/Tools

SharePoint 2010 Branding

SharePoint 2010 Videos

SharePoint 2013

SharePoint Online

SilverLight

SPServices

SQL Server

Sql Server 2012

SSIS

Web Optimization Tools

Web Performace Tools

Windows 8

Windows Azure

Windows Azure

Windows Phone

WorkFlows

WSS V3

xml

When you create a database i'm sure you can grant users/roles access to stored procedures tables etc..
But if you inherit the sql database then you will need to find a solution

Thanks to Jeremy Kadlec from sql tips

Unfortunately, with all of the security changes in SQL Server 2005, no default role is available to execute all stored procedures in a given database.Quite sure the same applies to SQL SERVER 2000.

But there is a solution looking at the script below:

Just pass in the loginname below to the stored proc

CREATE PROCEDURE spGrantExectoAllStoredProcs @user loginname
AS


SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @CMD1 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 u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P'
AND o.[Name] NOT LIKE
'dt_%'

-- 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 EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' +
@user

-- 8 - Execute the string
-- SELECT @CMD1
EXEC(@CMD1
)

-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures

SET NOCOUNT OFF
GO


Also the stored procedure will only grant privileges to stored procedures and not stored functions.  To grant to both types change section 3's insert from:
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'dt_%'
AND ROUTINE_TYPE = 'PROCEDURE'

 to:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'dt_%'
AND DATA_TYPE <> N'TABLE'
AND ( ROUTINE_TYPE = N'PROCEDURE'
OR ROUTINE_TYPE = N'FUNCTION')




posted on Tuesday, December 16, 2008 5:43 PM

Feedback

# re: How to Grant Execute to all SQL Server Stored Procedures/Functions 12/31/2008 3:56 AM kat
THANKYOU THANKYOU THANKYOU

You are a lifesaver.

Kat

# re: How to Grant Execute to all SQL Server Stored Procedures/Functions 10/7/2009 12:04 PM Chris C
Or for SQl 2005/SQL 2008 use the following:
/* CREATE A NEW ROLE */
CREATE ROLE roleProductionSupportReadWrite


/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO roleProductionSupportReadWrite


Post A Comment
Title:
Name:
Email:
Comment:
Verification: