Create a Table only Schema

Question

We use JPA to create the objects that map to our database tables, however whenever it starts looking at our views the entire entity generation process grinds to a halt.

Is there a way in SQL Server to create a schema that only has access to the tables in our database?

Answer 1

Ok so this isn't perfect put it works well enough for me. Note this is a development database where we're trying to make life easier for the developers rather than a production database.

What i did was create a TablesOnly user that people must login with when they're generating entities. I had 2 scenarios that I had to deal with - Current tables and tables that will be created in the future.

First I ran this cursor to allocate the current tables to my 'TablesOnly' user

DECLARE @username VARCHAR(50) -- username  
DECLARE @tablename VARCHAR(50) -- database name  

SELECT @username = 'tablesOnly'

DECLARE db_cursor CURSOR FOR  
SELECT name FROM sys.tables


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @tablename   

WHILE @@FETCH_STATUS = 0   
BEGIN   

        EXECUTE ('GRANT SELECT ON '+@tablename+' TO '+@username)
        EXECUTE ('GRANT VIEW DEFINITION ON '+@tablename+' TO '+@username)

        FETCH NEXT FROM db_cursor INTO @tablename   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Then I created this trigger on a table create that automatically allocated the permissions that I need to the user

CREATE TRIGGER UpdateTablesOnlyUser 
ON DATABASE 

    FOR CREATE_TABLE
    AS 
        set nocount on

        DECLARE @data XML, @ObjectName sysname
        set @data = EVENTDATA()
        SET @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')


        PRINT 'GRANT SELECT ON '+@objectName+' TO tablesOnly'
        PRINT 'GRANT VIEW DEFINITION ON '+@objectName+' TO tablesOnly'
        EXECUTE ('GRANT SELECT ON '+@objectName+' TO tablesOnly')
        EXECUTE ('GRANT VIEW DEFINITION ON '+@objectName+' TO tablesOnly')