locked
limit developer access to stored procedures in a specific schema RRS feed

  • Question

  • I want to grant a developer access to a database and to:

    view only certain tables in the dbo schema 

    create/edit stored procedures, views and functions in one specific schema (different from the dbo schema)

    How do I do this?

    Friday, January 11, 2019 9:50 PM

Answers

  • Create a Role for the Developers.  Grant the SELECT permissions you want to that role, and make the role the owner of the target schema.  This will give the developers CONTROL on the target schema, and break the ownership chains between objects in that schema and objects in other schemas.


    eg:

    create schema dev
    go

    create role developers grant select on dbo.SomeTable to developers grant select on dbo.AnotherTable to developers grant create view, create function to developers alter authorization on schema::dev to developers



    Microsoft Technology Center - Dallas
    My blog


    Friday, January 11, 2019 10:19 PM

All replies

  • Create a Role for the Developers.  Grant the SELECT permissions you want to that role, and make the role the owner of the target schema.  This will give the developers CONTROL on the target schema, and break the ownership chains between objects in that schema and objects in other schemas.


    eg:

    create schema dev
    go

    create role developers grant select on dbo.SomeTable to developers grant select on dbo.AnotherTable to developers grant create view, create function to developers alter authorization on schema::dev to developers



    Microsoft Technology Center - Dallas
    My blog


    Friday, January 11, 2019 10:19 PM
  • Thank you.
    Friday, January 11, 2019 11:22 PM