GRANT User Permissions to Create, Drop Alter all Objects is a schema

Question

GRANT User Permissions to Create, Drop Alter all Objects is a schema

Welsh Corgi

SSC Guru

Points: 116520
More actions
September 28, 2016 at 1:30 pm

#321845

It has been a while since I Granted a User Permissions to Create, Drop Alter, etc. all Objects is a schema.
I need a simple solution to do this for a single User.
I want to make sure I get it right. Something simple.
Any help would be greatly appreciated.
Thank you.
Edit: Can't I just make them the owner of the schema?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246716 More actions · Answer 1

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Making the user the owner of the schema will work.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eric M Russell SSC Guru Points: 125524 More actions · Answer 3

Reading the following article, it sounds like in addition to granting user as owner of the schema, you must also grant them permission to perform specific operations like creating tables. If the user started in public role, then they can then create, select, drop, etc. tables within the schema but not within other schemas like dbo. You'll need to confirm if this is the case.

https://social.msdn.microsoft.com/Forums/en-US/8a3fded9-220d-477f-b39a-aa3f4fa26bec/how-can-i-grant-permissions-to-only-schema-to-a-user?forum=ssdsgetstarted

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

How do I grant to CREATE PROCEDURE for User Canada on schema ca?

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

GRANT CREATE PROCEDURE for User Canada on schema ca?

GRANT CREATE PROCEDURE ON ::ca TO Canada WITH GRANT OPTION;

I do not need the WITH GRANT Option.

Does that look right?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I get a syntax error on the following command:

GRANT CREATE PROCEDURE ON SCHEMA ::ca TO CCUser;

What is wrong with this statement?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What's the error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

GilaMonster (9/29/2016)
What's the error?

Msg 102, Level 15, State 1, Line 0

Incorrect syntax near 'CREATE PROCEDURE'.

I get the same error ON ALTER and DDROP PROCEDURE.

Also,I get the same error ON CREATE TABLE, ALTER TABLE and DROP Table.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Phil Parkin (9/28/2016)
See link.

And, after you're read that, tell me whether 'CREATE PROCEDURE', 'ALTER PROCEDURE', 'DROP TABLE', etc are valid permissions to be granted on a schema.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

I found this before I read the link.

Edit: Before I read your post.

As CREATE PROCEDURE isn't a schema-level permission, just try GRANT CREATE PROCEDURE TO [domain\user].

I tried this and I get the error:

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'ALTER'.

GRANT ALTER PROCEDURE TO CCUser;

GRANT ALTER PROCEDURE TO CCUser;

I have the same problem with DROP PROCEDURE.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eric M Russell SSC Guru Points: 125524 More actions · Answer 11

GilaMonster (9/29/2016)
Phil Parkin (9/28/2016)
See link.
And, after you're read that, tell me whether 'CREATE PROCEDURE', 'ALTER PROCEDURE', 'DROP TABLE', etc are valid permissions to be granted on a schema.

I assume the goal here is something like sandboxing specific users into specific schemas in the development environment, rather than creating additional databases.

Is that right, Corgi?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Eric M Russell (9/29/2016)
GilaMonster (9/29/2016)
Phil Parkin (9/28/2016)
See link.
And, after you're read that, tell me whether 'CREATE PROCEDURE', 'ALTER PROCEDURE', 'DROP TABLE', etc are valid permissions to be granted on a schema.
I assume the goal here is something like sandboxing specific users into specific schemas in the development environment, rather than creating additional databases.
Is that right, Corgi?

Yes that is right.

I created a Database and perform an ETL Load nightly to refresh the Data which is limited to Canada.

I only want them to be able to create, alter and drop objects in their own schema.

Technically it is a Production Database.

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eric M Russell SSC Guru Points: 125524 More actions · Answer 13

Welsh Corgi (9/29/2016)
Eric M Russell (9/29/2016)
GilaMonster (9/29/2016)
Phil Parkin (9/28/2016)
See link.
And, after you're read that, tell me whether 'CREATE PROCEDURE', 'ALTER PROCEDURE', 'DROP TABLE', etc are valid permissions to be granted on a schema.
I assume the goal here is something like sandboxing specific users into specific schemas in the development environment, rather than creating additional databases.
Is that right, Corgi?
Yes that is right.
I created a Database and perform an ETL Load nightly to refresh the Data which is limited to Canada.
I only want the to be able to create objects in their schema.
Technically it is a Production Database.
Thanks.:-)

It would probably be easier if you CREATE whatever objects they need, and then the ETL users are simply granted select, insert, delete permission on the schema.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Welsh Corgi (9/29/2016)
GRANT ALTER PROCEDURE TO CCUser;
I have the same problem with DROP PROCEDURE.

Have you even bothered to read the linked page?

The page that lists EXACTLY what are valid permissions to be granted on a schema?

And if you want to now grant database-level permissions, then try this page https://msdn.microsoft.com/en-us/library/ms178569.aspx, which has all the valid permissions nicely listed at the bottom.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass