Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
It sounds like your best approach may be to create a database user that only have rights to tables, and have your entity-creating application use that database principal to connect. –  Thomas Stringer Jun 19 '12 at 12:38
    
Is there a way to make the user get all tables? Specifically I'd want new tables to be automatically added to his security, otherwise it just becomes a pain in the ass to have to constantly update it. –  Tim Sparg Jun 19 '12 at 12:59
    
Put your tables in SchemaX, "other stuff" in SchemaY, grant the user access to just SchemaX? However, feels "icky" doing this just to satisfy the needs of a development tool. –  Mark Storey-Smith Jun 19 '12 at 22:18
add comment

1 Answer 1

up vote 1 down vote accepted

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')
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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