Post reply

Granting Execute, ALTER, Create permission for all SPs

  • April 6, 2011 at 8:30 am

    #235087

    Hi Guys,

    I am having trouble with security in SQL Server 2008 R2. I am setting up a dev database and I would like to prevent developers from making change to tables but I would like to give them permissions to Alter, create, execute all the SPs and views. They should also have data reader access to the data in the tables. I tried a few combinations but nothing worked for me. I would appreciate any comments , ideas etc

  • April 6, 2011 at 1:35 pm

    #1308254

    You will need to grant the appropriate CREATE statements. In 2005 and above, you will also need to grant ALTER SCHEMA on those schema where these objects appear. The problem with this is that it does permit altering and dropping tables. So to prevent this, you'll need to create a DDL trigger which intercepts these commands and rolls them back.

    K. Brian Kelley
    @kbriankelley

  • April 13, 2011 at 1:22 pm

    #1311280

    First create the user in the database, the issue this statement.

    Make them members of database role 'db_datareader'

    and grant them access to create views and SP.

    They will not have access to make any changes on the tables, but yes on the stored procedures.

    EXEC sp_addrolemember N'db_datareader', N'MyDomain\MyUserName';

    GRANT CREATE PROCEDURE TO [MyDomain\MyUserName];

    GRANT CREATE VIEW TO [MyDomain\MyUserName];

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

Viewing 3 posts - 1 through 2 (of 2 total)

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