How to restrict user access to tables, but not to SPs and Views in SQL Server 2008

Question

Prerequisites:

Let's say I have 2 tables.

1. Table A With columns A, B and C.

2. Table B with columns A, B and C.

I also have a stored procedure to update both tables and I have a simple View that joins the tables.

I also have a user, let's call him... "Bob".

Question:

Now;

I want "Bob" to only have access to the view and the stored procedure. "Bob" cannot gain read/write to either table, only to the view and the stored procedure.

How would I achieve this?

Answer 1

Old question, but ownership chaining in SQL is the answer. The comments to the question had the answer to the SP issue, but OP implied an issue with running select against the view.

For the SP, you need to specify the execute as owner clause

CREATE PROCEDURE dbo.YourProc
WITH EXECUTE AS OWNER
AS
   --Your code here
GO

GRANT EXEC ON dbo.YourProc TO Bob;
GO

And if the owner of the SP has access to those tables, this will work.

For the view, same idea. I stumbled because I created a new view/schema to limit access, I gave the user select permission on the view, but the owner of the schema did NOT have access to the underlying tables, so when the user executed the select, I got an error about not having permissions to run select against the underlying table. Changing the owner of the schema to dbo (which owned the tables) fixed this for me.

I put together a script to stage this a bit, and gives the user select permissions on the schema instead of just the view (as I know this won't be the only view they'll want)

DECLARE @Schema varchar(128) = 'Reporting', @Username varchar(255) = 'ReportingUser', @dbName varchar(255)

SELECT @dbName=db_name(dbid) FROM master.dbo.sysprocesses WHERE spid=@@spid

IF NOT EXISTS (SELECT * FROM master.sys.server_principals where name = @Username)
BEGIN
    Print 'User must be created/added to SQL before being given database permissions'
    GOTO TheEnd
END

IF NOT EXISTS (SELECT * FROM sysusers where name = @Username)
BEGIN
    EXEC sp_grantdbaccess @Username,@Username
END

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @SCHEMA)
BEGIN
    EXEC('CREATE SCHEMA ' + @Schema)
END

EXEC ('ALTER USER '+@Username+' WITH DEFAULT_SCHEMA = ' + @Schema)

EXEC ('GRANT SELECT ON SCHEMA :: '+ @Schema +' TO '+ @Username)

TheEnd:

And to save some folks to stumble over this too some time, here are some helpful queries to identify and troubleshoot ownership:

SELECT s.name AS SchemaName, 
       s.schema_id, 
       u.name AS schema_owner
FROM sys.schemas s
     INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name;

This will show you schema owners

exec sp_tables @table_type = "'table', 'view'"

This will show you owner of the views/tables