SQL Server – Grant User Rights to Create Stored Procedures in Custom Schema

permissionsschemasql serversql server 2014stored-procedures

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?

Best Answer

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;

Related Solutions

SQL Server – db_owner Role Cannot Create Stored Procedures

Interesting one - difficult to pin this one down. Have you thought about looking at the public role?

sp_helprotect 'CREATE PROCEDURE',NULL,NULL,'s'

Does that bring you back anything?

SQL Server – Query with Date Ranges

Not sure where you copied this code from, but it looks like it came from a web site that translated minus signs (-) to long dashes (–). You can see this in Management Studio if you turn IntelliSense on:

Here is the easy way to compare the same range as the year before:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

SELECT @startdate, DATEADD(YEAR, -1, @startdate),
       @enddate,   DATEADD(YEAR, -1, @enddate);

This compares the same period as last year, but I'm not sure that's what you're after since you are also using the zero date and datediff - not clear why. It does not account for leap year; you'll need to define what should happen if the range involves 2/29 either this year or last year.

When you use those dates in a query, don't do this subtracting 3 milliseconds nonsense - it only leads to problems. If you're trying to get all the data for February 12th even if it has time, then use < DATEADD(DAY, 1, @enddate). Also, don't use YY, just type out YEAR. It is also far easier to get the full range of last year:

-- for SQL Server 2012+
WHERE col >= DATEFROMPARTS(YEAR(@startdate)-1, 1, 1)
  AND col <  DATEFROMPARTS(YEAR(@startdate), 1, 1);

-- for older, unsupported versions:
WHERE col >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
  AND col <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

To sum up:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

-- sales from that period this year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= @startdate
    AND some_datetime_column <  DATEADD(DAY, 1, @enddate);

-- sales from same period previous year (again, not accounting for leap special case):

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, -1, @startdate)
    AND some_datetime_column <  DATEADD(YEAR, -1, DATEADD(DAY, 1, @enddate));

-- sales from all of previous year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
    AND some_datetime_column <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

Best Answer

Related Solutions

SQL Server – db_owner Role Cannot Create Stored Procedures

SQL Server – Query with Date Ranges

Related Question