9

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?

1 Answer 1

14

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.

5
  • "GRANT EXEC ON dbo.test TO [user1], [user2], ...;" Is it possible to grant the users by using drop and click in Sql server instead of using T-SQL code?
    – DWD
    Dec 12, 2013 at 19:58
  • @DWD Why would you want to do that? For one, you can't very easily put your points and clicks in source control, or tell your co-workers exactly what you did. Dec 12, 2013 at 20:03
  • You can, but I agree with Aaron here. Also, this works because the object being accessed by the stored procedure is owned by the same principal as the stored procedure. Read up on ownership chaining if you want a deeper understanding of why it works, and where it doesn't.
    – db2
    Dec 12, 2013 at 20:17
  • You could set up a database role, give the role access to the SPs, and add the users to the role. That X (for # of SPs) + Y (for the # of users) instead of X * Y. I often do that with a role 'db_executor' which I give EXECUTE rights on the schema (X = 1 no matter how many SPs then), give the users db_datareader and db_executor membership, and that would be a most basic level of security. (Might split db_executor into several roles if it's required) Aug 19, 2015 at 2:36
  • @Roger That assumes that all of the users should have the same access to all of the objects. I find that in most cases either (a) they all connect as the same SQL login anyway, or (b) there are enough exceptions to the "rule" that the efficiencies gained by using a role are wiped out. In this case, the OP is only trying to give rights to a single procedure, so if that really is the end of the story, you either have to Y ALTER ROLE statements or Y GRANT EXEC statements. Coin flip (again, if that really is the end of the story). Aug 19, 2015 at 2:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.