CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2012permissions
Neil Weicher
Neil Weicher

Reputation: 2502

Grant execute to stored procedure without SELECT permissions to underlying table

I don't think this is possible, but I hope I am missing something. Let's say I have this stored procedure in SQL Server 2012 SP3:

CREATE PROCEDURE [dbo].[myproc] 
AS 
    SELECT * FROM [dbo].[mytable]

I want to grant a database principal the permission to execute [myproc] without granting the permission to SELECT on the underlying table.

I have tried all sorts of GRANT statements with no luck. When I execute myproc, I still get the error

select permission denied on mytable

The database principal is not the DB Owner and I do not want to make him the DB Owner.

Is what I am trying to do possible?

Thanks.

Upvotes: 2

Views: 8927

Answers (3)

Thomas Spankie
Thomas Spankie

Reputation: 11

If you don't want to make the DB Principle the owner then you need to grant it permissions with 'Grant With' in order to have them passed on further.

Assuming 'dbo' is the owner and 'ElevatedUser' is a new user with select permissions 

GRANT EXECUTE ON [dbo].[myproc] TO ElevatedUser 
WITH GRANT OPTION  AS [dbo]

GRANT EXECUTE ON [dbo].[myproc] TO MyUser AS ElevatedUser

or if you are really stuck you can put it inside the stored procedure itself

CREATE PROCEDURE [dbo].[myproc] 
WITH EXECUTE AS 'dbo' 
AS
BEGIN

Upvotes: 1

Urvashi
Urvashi

Reputation: 1

GRANT EXECUTE ON SPNAME TO DBUSER

Upvotes: 0

Chris Beardsley
Chris Beardsley

Reputation: 71

Weicher, Unless there's a specific deny on the underlying table this grant statement should work:

GRANT Exec [dbo].[myproc] TO MyUser;
GO

Test it with code like this:

EXECUTE AS USER = 'MyUser';
GO
EXEC [dbo].[myproc]; -- should work
GO
SELECT id FROM [dbo].[table]; -- should fail
GO
REVERT;

When I've changed code six ways to Sunday, I like to start clean to make sure I didn't bollix something in inadvertently and be very purposeful. step. by. step.

  1. Make a test table, Grant MyUser read rights - can you run a select using the execute as user code?
  2. Remove read rights, does the select work? If it didn't, the world is still sane (somewhat). Now try with a new stored procedure
  3. Grant execute rights to MyUser - does it work?

If it does, then follow the same test on the actual table - Can you grant read rights, then can read rights be removed. If so recreate the SP and try again.

And sorry if that was too simplistic, no offense intended just wanted to make sure we were on the same page testing wise.

If it doesn't work, post the results of the test and I'll try to help further.

Upvotes: 2

Related Questions