SQL Server 2008 - Restricting DDL Access

Question

Post reply

SQL Server 2008 - Restricting DDL Access

naramsg

SSC Eights!

Points: 996
More actions
November 21, 2008 at 1:39 am

#196111

Hi,
We have a SQL 2008 DB Server on which resides two databases: A & B.
We have an application accessing these two DBs to insert, update and delete data from/to the tables and to execute stored procedures in both DBs. The application accesses the DB through an user called X.
We would like to restrict access so that X does not have DDL permissions on both databases, and should only be able to select and not insert/update/delete the data.
We have tried giving the following rights to X:
db_datareader
db_datawriter
public
but with these rights, the user is not able to execute stored procedures. Currently, since this is a live environment, we have given db_owner privileges to the user.
My question: how do we remove DDL access to the user while giving execute permission to all stored procedures in the databases?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

How about granting exec to the user?

Such as grant exec on {stored proc name} to {X}

DAB

naramsg SSC Eights! Points: 996 More actions · Answer 2

I could do this, but there are about 300-400 procedures, and newer ones would be created on a regular basis.

Isn't there a way you can give db_owner privileges but revoke DDL privileges, something similar to NTFS security where one can give users full rights to a disk folder but revoke the write permissions?

naramsg SSC Eights! Points: 996 More actions · Answer 3

Hi,

Is there no way to do this? Someone help!

Regards,

Guru

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

You can GRANT EXEC on the schema. This would allow the user to execute all stored procedures in the schema, even when you create new ones.

[font="Verdana"]Markus Bohse[/font]

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

By the way if you only want your user to d select and executes you should remove him from the datawriter role.

[font="Verdana"]Markus Bohse[/font]

Benjamin Lotter-629969 Old Hand Points: 301 More actions · Answer 6

You should do this through a new database role that you create.

Here's a simplified example

USE A

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

Now add the role db_executor to the X user in that database. This will cover all stored procedures in the entire database. You could combine this with another suggestion to limit it to stored procedures within a certain schema.

The biggest problem with doing the grants to the stored procedures (other than having to remember to apply to new SPs) is that you don't have a an easy way to see what permission the user has on the database. By creating a custom database role, you more effectively manage your permissions at any level of granularity.

There are plenty of good articles and helps about using database roles. You should also brush up on schemas for definining granular permissions on database objects.

Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4

naramsg SSC Eights! Points: 996 More actions · Answer 7

Dear Markus and Benjamin,

Thanks for your solutions. I have created the database role, granted execute permissions and have mapped the db_dataexecutor and db_datareader roles to the user. Everything seems to be working fine now.

Thank you very much! 🙂

Regards,

Guru