I need to allow users to create views on our database using SQL Management studio, basically the user should be able to read data from all of the tables, execute any function and create/alter views. They should not be able to alter/drop any tables/procedures/functions/etc.
What I have come up with so far is:
CREATE LOGIN MyLogin WITH PASSWORD='MyPa$$w0rd'
CREATE USER MyUser FROM LOGIN MyLogin
EXEC sp_addrolemember db_datareader, MyUser
EXEC sp_addrolemember db_denydatawriter, MyUser
GRANT CREATE VIEW TO MyUser
GRANT EXECUTE TO MyUser
GRANT ALTER ON SCHEMA::[dbo] TO MyUser
This works up to a point i.e.
- can read data
- cannot add/delete/update data
- can create views
- can execute functions
However this means that they can also alter/drop tables/functions/procedures.
My next thought was to use the INFORMATION_SCHEMA to loop though each table/function/procedure and manually deny ALTER and DROP but I cannot find a way to do this, if I try:
DENY DROP ON MyTable TO My User
It says "Incorrect syntax near 'DROP'."
Does the fact that I given the user rights to alter the schema, which is required in order to create a view, mean that I cannot stop them from dropping other objects in the database?