limit developer access to stored procedures in a specific schema

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?

Answer 1

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

Answer 2

Thank you.