CODEWITHSUNDEEP
sql-server-2008t-sql
Karthik D V
Karthik D V

Reputation: 926

How to GRANT SELECT permission to all procedures

To secure the database interaction, I have created a restricted user who can only execute the stored procedures. He doesn't have any rights to query any table.

This scenario I have implemented very well!. Now the problem is, one of my stored procedure involves dynamic SQL execution, which fails the execution by saying that I don't have the permission to run SELECT query on table X.

To avoid this, I need to provide explicit SELECT permission to such procedures NOT TO USERS.

Please help me!

Upvotes: 0

Views: 14125

Answers (5)

SQLburn
SQLburn

Reputation: 129

On the SP where sql is complaining about you not having the permissions to run a SELECT query on table X, do you have a broken chain of ownership on that particular SP and table?

Upvotes: 0

Peter Schott
Peter Schott

Reputation: 4726

MNagel has the correct link for this, but to reiterate, you want to look at using something like:

CREATE PROC dbo.MyProc
WITH EXECUTE AS 'SomeUser'
AS
BEGIN --Proc
 --Do Stuff
END --Proc
GO

"Execute As" has some other gotchas along the way. You have to allow others to impersonate the specified user and that user would need the appropriate select permissions to the underlying tables.

If you want to mass grant permissions, you can do that at the Schema level instead of the object level - something like:

GRANT SELECT, EXEC ON Schema::dbo TO MyRole;

I've used that before to greatly simplify a lot of grant statements for our roles. We have very few overrides for the more general roles so this helps quite a bit.

Upvotes: 2

KeyboardFriendly
KeyboardFriendly

Reputation: 1798

Is this what you want?

USE DatabaseName
GO
-- 1 - db_executestoredprocedures
-- 1a - Create role
CREATE ROLE db_executestoredprocedures
GO
-- 1b - Grant permissions
GRANT EXECUTE TO db_executestoredprocedures
GO

-- 2 - db_selecttablevaluedfunctions
-- 2a - Create role
CREATE ROLE db_selecttablevaluedfunctions
GO
-- 2 - Create permissions
DECLARE @Function_Name nvarchar(250);
DECLARE @CMDEXEC1 nvarchar(2000);
DECLARE db_cursor CURSOR FOR  
SELECT [name]
FROM sys.objects
WHERE Type = 'TF'
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @Function_Name
WHILE @@FETCH_STATUS = 0   
BEGIN

 SET @CMDEXEC1 = 'GRANT SELECT ON [' + @Function_Name + '] TO db_selecttablevaluedfunctions;'
 --- SELECT @CMDEXEC1 
 EXEC(@CMDEXEC1)
 FETCH NEXT FROM db_cursor INTO @Function_Name
END
CLOSE db_cursor   
DEALLOCATE db_cursor
GO

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44356

There is no build in function to give execute to a user.

Create a role with execute permission and add that role to the user.

CREATE ROLE db_executer
GRANT EXECUTE to db_executer
EXEC sp_addrolemember N'db_executer', N'<username>'

Upvotes: 4

mnagel
mnagel

Reputation: 6854

define the stored procedure to be executed as a user with appropriate rights:

http://msdn.microsoft.com/en-us/library/ms188354.aspx

Upvotes: 0

Related Questions