asked
schema security
Hello, I’d like to give a developer full control in a schema while keeping him out of everything else outside that schema. My problem is he is unable to create stored procedures. The environment is SS2K5 sp3. Here’s what I did: USE [FIN88DEV] GO create schema CASH ------------------------------- USE [master] GO CREATE LOGIN [jdoe] WITH PASSWORD=N'jdoe', DEFAULT_DATABASE=[FIN88DEV], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ------------------------ USE [FIN88DEV] GO CREATE USER [jdoe] FOR LOGIN [jdoe] GO USE [FIN88DEV] GO ALTER AUTHORIZATION ON SCHEMA::[CASH] TO [jdoe] GO ------------------------ ------------------------ create proc CASH.sp_test as select 'x' Msg 262, Level 14, State 1, Procedure sp_test, Line 1 CREATE PROCEDURE permission denied in database 'FIN88DEV'. -- From BOL I got the impression that the ALTER AUTHORIZATION should have provided the necessary permissions. I’ve hit a wall, thank you in advance for your help. Regards, df
In BOL it says the following about creating SPs: > **Permissions:** Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.
