Rights to execute stored procedure, but not read underlying tables directly

Question

I have a stored procedure that contains lots of different columns from many different tables; I want to give 20 users access to the results of the stored procedure via Excel.

Is it possible to make the user to get access only to the stored procedure, and not to the underlying tables and views?

Answer 1

Sure, you just grant rights to those users to the stored procedure, not to the underlying table.

CREATE PROCEDURE dbo.test -- always use schema prefix!
AS
BEGIN
  SET NOCOUNT ON;

  SELECT t1.col1, t2.col2, ...
    FROM dbo.table1 AS t1
    INNER JOIN dbo.table2 AS t2
    ON ... ;
END
GO

GRANT EXEC ON dbo.test TO [user1], [user2], ...;

Here is a completely contained example that you can test. First, create a database (hopefully floob does not conflict with any database you already have):

CREATE DATABASE floob;
GO

Now, create two logins:

CREATE LOGIN floob1 WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
CREATE LOGIN floob2 WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

And create database users to go with them:

USE floob;
GO
CREATE USER floob1 FROM LOGIN floob1;
CREATE USER floob2 FROM LOGIN floob2;
GO

Now, create a table that they don't have access to by default, and a stored procedure that queries it:

CREATE TABLE dbo.whatwhat(id INT);
INSERT dbo.whatwhat(id) VALUES(1);
GO
CREATE PROCEDURE dbo.select_whatwhat
AS
BEGIN
  SET NOCOUNT ON;

  SELECT id FROM dbo.whatwhat;
END
GO

Now, grant execute rights to floob1 only:

GRANT EXEC ON dbo.select_whatwhat TO floob1;
GO

Now, try to execute the procedure and select from the table as floob1:

EXECUTE AS USER = 'floob1';
GO
EXEC dbo.select_whatwhat; -- works
GO
SELECT id FROM dbo.whatwhat; -- fails with error
GO
REVERT;

You get a successful query resulting from executing the stored procedure, but an error message when trying to query the table directly, because you haven't been explicitly granted access to do so.

Msg 229, Level 14, State 5
The SELECT permission was denied on the object 'whatwhat', database 'floob', schema 'dbo'.

Now, try as floob2, who hasn't been given explicit access to execute the stored procedure, either:

EXECUTE AS USER = 'floob2';
GO
EXEC dbo.select_whatwhat; -- fails with error
GO
SELECT id FROM dbo.whatwhat; -- fails with error
GO
REVERT;

This time you get the same error on the SELECT, but also this error on the EXEC:

Msg 229, Level 14, State 5, Procedure select_whatwhat
The EXECUTE permission was denied on the object 'select_whatwhat', database 'floob', schema 'dbo'.

Don't forget to clean up:

USE master;
GO
DROP DATABASE floob;
DROP LOGIN floob1;
DROP LOGIN floob2;
GO

Regarding your comment:

Is it possible to grant the users by using drop and click in Sql server instead of using T-SQL code?

It is, but that doesn't sound like a very good idea. Here is the process: you right-click the stored procedure in Object Explorer, hit Properties, move to the Permissions tab, hit Search..., type your username, hit OK, then check the checkbox where EXECUTE on the left meets GRANT on the top, and click OK. Now repeat for every stored procedure, and multiply that by 20 (all your users). Yeah, that sounds a lot easier than writing a script. Here is a better way to generate the script you need in one step, with a single copy, paste and execute instead of who knows how many points and clicks you'll need to do this interactively through the GUI:

DECLARE @xml NVARCHAR(MAX) = N'';

;WITH users AS 
(SELECT name = 'user1' UNION ALL SELECT 'user2' ... UNION ALL SELECT 'user20')
SELECT @xml += N'GRANT EXEC ON ' + QUOTENAME(s.name) + 
  '.' + QUOTENAME(p.name) + ' TO ' + QUOTENAME(u.name) + ';
'
FROM users AS u CROSS JOIN sys.procedures AS p
  INNER JOIN sys.schemas AS s ON s.[schema_id] = p.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

You could also add sys.database_principals to the mix to make sure you're getting the right set of users with no typos.