Post reply

Create Role for Developers on Database Server

  • February 29, 2012 at 7:29 am

    #391681

    I want to create a "Developer" role within SQL 2008. The role should be allowed to read anything, and view the definitions of SPs/Functions, etc. I do not want them to be able to alter SPs/Tables, etc.

    Any direction?

    Thanks !

  • February 29, 2012 at 7:33 am

    #1453163

    Create a Developer role in each database and grant it the necessary permissions.

    Then add the developers to that role in each database they need access to.

  • February 29, 2012 at 7:35 am

    #1453165

    checkai (2/29/2012)

    I want to create a "Developer" role within SQL 2008. The role should be allowed to read anything, and view the definitions of SPs/Functions, etc. I do not want them to be able to alter SPs/Tables, etc.

    Any direction?

    Thanks !

    something like this get you started?

    Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

    USE [WHATEVER]

    Create USER [ClarkKent] FOR LOGIN [ClarkKent]

    CREATE ROLE [MyDevelopers]

    --allow the users to read data

    EXEC sp_addrolemember N'db_datareader', N'MyDevelopers'

    --allow the users to see view proc and function definitions

    Grant View Definition ON SCHEMA::[dbo] To [MyDevelopers]

    --finally add our user to the role:

    EXEC sp_addrolemember N'MyDevelopers', N'ClarkKent'

    --test:

    EXECUTE AS USER='ClarkKent'

    --who am i?

    select suser_name()

    --do stuff

    --change back into superman

    REVERT;

    --clean up after ourselves

    /*

    DROP ROLE [MyDevelopers]

    DROP USER [ClarkKent]

    DROP LOGIN [ClarkKent]

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 29, 2012 at 7:39 am

    #1453167

    Thanks, will this allow them to read SP definitions?

  • February 29, 2012 at 7:48 am

    #1453176

    checkai (2/29/2012)

    Thanks, will this allow them to read SP definitions?

    yep, the grant here does it:

    --allow the users to see view proc and function definitions

    Grant View Definition ON SCHEMA::[dbo] To [MyDevelopers]

    you can read about the specifics ov VIEW DEFINITION in books online here:

    msdn.microsoft.com/en-us/library/ms175808.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

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