I’m sure some of you have wanted to do this:

ALTER SCHEMA Steve AUTHORIZATION Steve

You realize this doesn’t work, and you can’t grant the user Steve, rights to his schema after it’s created. You can do this:

CREATE SCHEMA Steve Authorization Steve

UPDATE: Someone pointed out this works after the fact:

ALTER AUTHORIZATION ON SCHEMA::Steve TO Steve

But not alter it. Strange and annoying. In my last post, I showed dropping and recreating the schema. That works well if you are beginning development, but not when you’re in the middle.

Let’s make this less confusing and see how we actually allow a developer to access a schema to create procedures (or other objects) when the schema exists.

First, let’s assume we want a developer, Steve, to be able to create procedures in the ETL schema. We have these conditions:

• The ETL schema exists
• The ETL schema is owned by another developer.
• The login and user, Steve, exists in this database with no permissions.

I want to now allow Steve to build the procedure ETL.MyProc.

Grant Permissions

The first thing I do is grant create procedure permissions to Steve.

CREATE LOGIN steve WITH PASSWORD = ‘Test';

GO

USE Sandbox

GO

CREATE USER Steve FOR LOGIN Steve

GO

GRANT CREATE PROCEDURE to Steve;

GO

With this done, now let’s set up our schema.

CREATE SCHEMA ETL

GO

There are no default permissions, so the user Steve cannot create ETL.MyProc right now. How do we fix this?

The trick here is that I need to allow Steve to ALTER the schema. I can do this by using this statement.

GRANT ALTER ON SCHEMA::ETL TO Steve;

GO

I could do other things. I could grant CONTROL. to Steve instead, but I might not want to do that. That gives Steve the ability to actually drop the schema, which probably isn’t want. It’s certainly not the “least permissions” to let the developer create objects in a schema.

Filed under: Blog Tagged: security, sql server, syndicated, T-SQL