Posted by masri999 on 09/22/06 04:29

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,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: SSIS Newbie help please.
• Thread view: Re: Create Procedure Permission ONLY

[Reply to this message]