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.