By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Create Procedure Permission ONLY

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
(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

P: n/a
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.