question

don 2 avatar image
don 2 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
sql-server-2005securityschema
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Blackhawk-17 avatar image
Blackhawk-17 answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

don 2 avatar image don 2 commented ·
That's it you solved my problem; thank you for taking the time to help out it is much appreciated. df
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
don if it helps you, mark it as an answer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.