Category: Blog

I was testing something the other day and realized this was a security area I didn’t completely understand. I decided to write a few posts to help me understand the issues.

I want to give a developer rights to create objects in a schema. In this case, I’ll stick with procedures, but the same thing would apply for tables, views, etc. How do I do this, allow someone to create objects in their schema?

Let’s create a login and user:

CREATE LOGIN steve WITH PASSWORD = ‘AR3allyStr0ng!P@**Wo9d’;
GO
USE Sandbox
GO
CREATE USER Steve FOR LOGIN Steve
GO
Now I have a user, and want them to be able to create this:

SETUSER ‘Steve’;

CREATE PROCEDURE Steve.MyProc
AS
    SELECT
            1;
RETURN

If the user does this, they get:

Msg 262, Level 14, State 18, Procedure MyProc, Line 3
CREATE PROCEDURE permission denied in database ‘sandbox’.

That’s no good.

We can see from the error that we don’t have writes to create procedures. Let’s fix that. First, we change our context and then we grant permissions.

SETUSER
GO

GRANT CREATE PROCEDURE TO Steve;

GO

With this done, let’s now try creating the procedure again with the SETUSER statement and the CREATE PROC statement. We then get:

Msg 2760, Level 16, State 1, Procedure MyProc, Line 5
The specified schema name "Steve" either does not exist or you do not have permission to use it.

This didn’t used to be the case in SQL 2000, where schemas didn’t exist. Now we don’t have any implicit schema for our user. Let’s see if we can make anything.

CREATE PROCEDURE MyProc
AS
SELECT 1;
RETURN
GO

Returns this:

Msg 2760, Level 16, State 1, Procedure MyProc, Line 11
The specified schema name "dbo" either does not exist or you do not have permission to use it.

At this point Steve doesn’t have permissions to any schema. Let’s start by adding a new schema.

CREATE SCHEMA Steve
GO

Once this is done, can I now create a procedure?

SETUSER ‘Steve’;

CREATE PROCEDURE Steve.MyProc
AS
    SELECT
            1;
RETURN

I get this:

Msg 2760, Level 16, State 1, Procedure MyProc, Line 5
The specified schema name "Steve" either does not exist or you do not have permission to use it.

The same error as before. This makes perfect sense because although the schema exists, I don’t have permissions to use it.

That’s the default in SQL Server. You don’t get any permissions by default. You need to explicitly set them.

In this case, I want Steve to have control of the schema [Steve], so I really want the user, Steve, to own it. How do I do this?

The key is that I want to use the Authorization clause with CREATE SCHEMA. I can’t use this with ALTER SCHEMA, only with CREATE SCHEMA.. so I need to do this:

SETUSER
GO
DROP SCHEMA Steve;
GO
CREATE SCHEMA Steve AUTHORIZATION Steve;
GO

Once this is done, I can now let my user create procedures.

SETUSER ‘Steve’
GO
CREATE PROCEDURE Steve.MyProc
AS
SELECT 1;
RETURN
GO
This works, and my developer can work in their own schema. Of course I need to ensure the developer has access to other objects, hopefully using a role of some sort that I’ve created for my application users.

 

SELECT SUSER_NAME();

DROP SCHEMA Bob
DROP SCHEMA steve

REVOKE CREATE SCHEMA FROM Steve

CREATE SCHEMA Steve AUTHORIZATION Steve

ALTER SCHEMA Steve AUTHORIZATION Steve

SETUSER ‘Steve’;
SELECT SUSER_NAME();

CREATE PROCEDURE Steve.MyProc
AS
    SELECT
            1;
RETURN

CREATE PROCEDURE MyProc2
AS
    SELECT
            1;
RETURN

SETUSER;
SELECT SUSER_NAME();

GRANT CREATE PROCEDURE TO Steve

SETUSER
DROP PROC steve.MyProc;
DROP PROC steve.MyProc2;
DROP SCHEMA Steve;