Post reply

SQL Server 2008 - Restricting DDL Access

  • 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?

  • November 21, 2008 at 8:48 am

    #901679

    How about granting exec to the user?

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

    DAB

  • November 22, 2008 at 2:45 am

    #902001

    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?

  • November 25, 2008 at 3:33 am

    #903007

    Hi,

    Is there no way to do this? Someone help!

    Regards,

    Guru

  • November 25, 2008 at 3:41 am

    #903012

    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]

  • November 25, 2008 at 3:42 am

    #903013

    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]

  • November 26, 2008 at 8:35 am

    #903806

    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

  • November 28, 2008 at 3:00 am

    #904644

    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

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

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