Database user permissions: GRANT VIEW DEFINITION but DENY on some object types

Question

I'm trying to figure out a combination of permissions that will allow a partner to see basic db schema but not expose objects including code. So,

1. VIEW and SELECT on TABLES (including keys)
2. Prevent/DENY on Views, Stored Procs, Functions.

I've thought about going through and denying on individual objects, but this won't protect any that are newly created.

Added consideration - I need to get this to work in SQL Azure.

Answer 1

You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.

Below is an example to get you started :

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the databas
GRANT 
      SELECT 

ON SCHEMA::dbo
      TO TableSelector 
GO

-- Add an existing user to the new role created 
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role 
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector 

Answer 2

I think maybe you just need an allowed list of objects. This can be done in a script as below or in the define Role as described by Kin. Below is an example of granting rights to a user using a table variable defining the list of allowed objects.

DECLARE @AllowedObjects AS TABLE(
   name SYSNAME
)

INSERT INTO @AllowedObjects
SELECT N'dbo.Table1'
UNION
SELECT N'dbo.Table2'

DECLARE @name SYSNAME
DECLARE names CURSOR FOR SELECT name FROM @AllowedObjects
OPEN names
FETCH NEXT FROM names INTO @name
WHILE(@@FETCH_STATUS <> -1)
BEGIN
  IF (@@FETCH_STATUS <> -2)
  BEGIN
        EXEC ('GRANT SELECT ON OBJECT::' + @name + ' TO userName')
  END
  FETCH NEXT FROM names INTO @name
END
CLOSE names
DEALLOCATE names

Answer 3

In the end I used datareader and datawriter roles to give access to the data. Then granted VIEW DEFINITION with the below proc. Will need to remember to maintain this...

Thanks all.

Create PROCEDURE GrantViewDefinitionOnTables  
(@login VARCHAR(30))  
AS  
begin
/* 
Included Object Types are:  
P - Stored Procedure  
V - View  
FN - SQL scalar-function 
TR - Trigger  
IF - SQL inlined table-valued function 
TF - SQL table-valued function 
U - Table (user-defined) 
*/  
SET NOCOUNT ON  

CREATE TABLE #runSQL 
(runSQL VARCHAR(2000) NOT NULL)  

--Declare @execSQL varchar(2000), @login varchar(30), @space char (1), @TO char (2)  
DECLARE @execSQL VARCHAR(2000), @space CHAR (1), @TO CHAR (2)  

SET @to = 'TO' 
SET @execSQL = 'Grant View Definition ON '  
SET @login = REPLACE(REPLACE (@login, '[', ''), ']', '') 
SET @login = '[' + @login + ']' 
SET @space = ' ' 

INSERT INTO #runSQL  
SELECT @execSQL + schema_name(schema_id) + '.' + [name] + @space + @TO + @space + @login  
FROM sys.all_objects s  
-- Want the view permission to sp, view, trigger then add type code mention in above. eg: where type IN('P','V','FN',TR','IF','TF','U')
WHERE type IN ('U')  
AND is_ms_shipped = 0  
ORDER BY s.type, s.name  

SET @execSQL = ''  

Execute_SQL:  

SET ROWCOUNT 1  

SELECT @execSQL = runSQL FROM #runSQL 

PRINT @execSQL --Comment out if you don't want to see the output 

EXEC (@execSQL) 

DELETE FROM #runSQL WHERE runSQL = @execSQL 

IF EXISTS (SELECT * FROM #runSQL)  
   GOTO Execute_SQL  

SET ROWCOUNT 0 

DROP TABLE #runSQL  
end


------------------------

EXEC GrantViewDefinitionOnTables 'MyDatabase'