469,273 Members | 1,779 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

Create Procedure Permission ONLY

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

Thanks

M A Srinivas

Sep 21 '06 #1
2 21697
(ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 21 '06 #2
Thank you Erland

Thanks
Srinivas
Erland Sommarskog wrote:
(ma******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Lauren Quantrell | last post: by
reply views Thread by Jean-Marc Blaise | last post: by
6 posts views Thread by Steve Richter | last post: by
4 posts views Thread by Karl | last post: by
5 posts views Thread by Michael Sperlle | last post: by
1 post views Thread by Matthew Wells | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.