Create Procedure Permission ONLY

[masri999@gmail.com]

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,S Ps,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,D ELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

Thanks

M A Srinivas

[Erland Sommarskog]

Re: Create Procedure Permission ONLY

(masri999@gmail .com) writes:

I have a requirement in SQL 2005 in Development database
>
1. Schema dbo owns all objects (tables,views,S Ps,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,D ELETE,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@sommarsk og.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

[masri999@gmail.com]

Re: Create Procedure Permission ONLY

Thank you Erland

Thanks
Srinivas
Erland Sommarskog wrote:

(masri999@gmail .com) writes:

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,S Ps,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,D ELETE,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@sommarsk og.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/prodinf...ons/books.mspx