• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

SQL User Allowed To Create/Maintain Views

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'
GO
CREATE USER MyUser FROM LOGIN MyLogin
GO
EXEC sp_addrolemember db_datareader, MyUser
EXEC sp_addrolemember db_denydatawriter, MyUser
GO
GRANT CREATE VIEW TO MyUser
GRANT EXECUTE TO MyUser
GRANT ALTER ON SCHEMA::[dbo] TO MyUser
GO

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?

0
3Si_pnewman
Asked:
3Si_pnewman
  • 4
  • 2
  • 2
1 Solution
 
LelikInsideCommented:
DENY DROP Table TO MyUser
0
 
LelikInsideCommented:
or try

use Database
go
deny drop to user
0
 
3Si_pnewmanAuthor Commented:
I still get the same error using that syntax, it says:

Incorrect syntax near 'DROP'.

The actual SQL I am using is:

DENY DROP ON BELIEF TO OMNETQueryUser

Or

DENY DROP BELIEF TO OMNETQueryUser

both return the same error message.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
3Si_pnewmanAuthor Commented:
Sorry I think I might have misunderstud what you ment.

I have now tried:

DENY DROP TABLE TO OMNETQueryUser
DENY DROP TO OMNETQueryUser

DENY DROP TABLE ON BELIEF TO OMNETQueryUser
DENY DROP TABLE BELIEF TO OMNETQueryUser

All return the same error.
0
 
Anthony PerkinsCommented:
>>Incorrect syntax near 'DROP'.<<
That would be because there is no "DENY DROP".
0
 
3Si_pnewmanAuthor Commented:

Thanks.  Going back to my original post then,

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?
0
 
Anthony PerkinsCommented:
That is my understanding.  Your best bet may be to add a DDL TRIGGER to prevent anyone changing the schema.
0
 
3Si_pnewmanAuthor Commented:

OK thanks.  I will give that a go.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now