Posted by Erland Sommarskog on 09/21/06 22:27

(masri999@gmail.com) writes:
> I have a requirement in SQL 2005 in Development database
>
> 1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
> 2. Only DBA's ( who are database owners ) can create, alter tables .
> Developer's should not create or alter tables .
> 3. Developers can create/alter Stored Procedure/User Defined functions
> in dbo schema and can execute SP/UDF.
> 4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
> tables in dbo schema
>
> How to achieve this using GRANT SCHEMA statement

The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:

CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev

GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev

CREATE TABLE mysig (a int NOT NULL)

EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Flattening Parent Child, an issue, please help
• Prev in forum: Re: Dyamic view/function based on table data (?)
• Thread view: Re: Create Procedure Permission ONLY

[Reply to this message]