2

I'm trying to grant SQL Server login rights to create stored procedures and bind them to a custom schema. In this case I've created a schema called IC. A service account will then be added to the schema with execute rights for the stored procedures.

My question is: how do I grant this SQL Server login the rights to create new stored procedures binded to IC?

I've tried GRANT CREATE ON SCHEMA::IC TO [username]; but only get

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CREATE'.

Any suggestions?

3

You can't change the ability to create procedures to only one schema by only granting permissions on the schema. (Assuming the user has no other rights.)

Why?

The user still needs the right to create objects in the database, which are in this case procedures.

What you can do, is grant the user CREATE PROCEDURE rights, and then either change the owner of the schema to that user (more secure, see below for more information) or grant that user permissions on the 'IC' SCHEMA.

Only granting CREATE PROCEDURE does not allow the user to create procedures on schema's such as dbo.

Create the login, the corresponding user and the IC schema

USE [master]
GO
CREATE LOGIN [TestIC] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [test]
GO
CREATE USER [TestIC] FOR LOGIN [TestIC];
GO
CREATE SCHEMA IC;

Grant Create procedure to the user

GRANT CREATE PROCEDURE TO [TestIC];

Either Change the owner of the schema (Safer Option)

ALTER AUTHORIZATION ON SCHEMA::IC TO [TestIC];

Or grant the user access to the schema

GRANT 
     ALTER

ON SCHEMA::IC


      TO [TestIC] ;

Risk involved with granting rights on the schema

Granting a user the ability to alter another user's schema gives that user the ability to SELECT, INSERT, UPDATE and DELETE rows in any table owned by the owner of that schema. This is called "Ownership Chaining" and it's what makes Views and Stored Procedures really simple ways to control security, as a user who has permissions on a View or Stored Procedure does not need to be granted permissions on the underlying Table, so long as the View/Proc is has the same owner as the Table.

Source

Take a look at the answer by @DavidBrowne-Microsoft for the origin of the quote, and to get more information on "ownership chaining" security risks when granting alter schema rights.

Test

EXECUTE AS LOGIN  = 'TestIC';

CREATE PROCEDURE IC.test
as
select * from sys.databases; 

Result:

Commands completed successfully.

This fails

CREATE PROCEDURE dbo.test
as
select * from sys.databases; 

Msg 2760, Level 16, State 1, Procedure test, Line 1 [Batch Start Line 35] The specified schema name "dbo" either does not exist or you do not have permission to use it.

Undo impersonation

REVERT;
0

You can try to give the ALTER permission:

GRANT ALTER ON SCHEMA::IC TO [username];

Docs

From the docs:

A user with ALTER permission on a schema can use ownership chaining to access securables in other schemas, including securables to which that user is explicitly denied access. This is because ownership chaining bypasses permissions checks on referenced objects when they are owned by the principal that owns the objects that refer to them. A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema's owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema's owner. When possible, you should avoid granting ALTER permission on a schema if the schema's owner also owns other schemas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.