Solved

SQL User Allowed To Create/Maintain Views

Posted on 2011-03-10
8
392 Views
Last Modified: 2012-05-11
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
Comment
Question by:3Si_pnewman
  • 4
  • 2
  • 2
8 Comments
 
LVL 2

Expert Comment

by:LelikInside
ID: 35094421
DENY DROP Table TO MyUser
0
 
LVL 2

Expert Comment

by:LelikInside
ID: 35094432
or try

use Database
go
deny drop to user
0
 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35094588
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35094616
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35095057
>>Incorrect syntax near 'DROP'.<<
That would be because there is no "DENY DROP".
0
 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35095218

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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 35095238
That is my understanding.  Your best bet may be to add a DDL TRIGGER to prevent anyone changing the schema.
0
 
LVL 1

Author Comment

by:3Si_pnewman
ID: 35095321

OK thanks.  I will give that a go.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question