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

Viewing 10 posts - 16 through 26 (of 26 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Cool, so you have a working GRANT statement for the schema, based on the permissions listed there?

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 2

GilaMonster (9/29/2016)
Cool, so you have a working GRANT statement for the schema, based on the permissions listed there?

Not yet, thank you.

Edit: I do not see where you can GRANT that permission at the schema level.

I was able to GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE.

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: 1004514 More actions · Answer 3

You grant ALTER on the schema, then the required CREATE permissions at the DB level, make sure the user has NO permissions to the dbo schema, doesn't have DB_Owner, doesn't have sysadmin.

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 4

GilaMonster (9/29/2016)
You grant ALTER on the schema, then the required CREATE permissions at the DB level, make sure the user has NO permissions to the dbo schema, doesn't have DB_Owner, doesn't have sysadmin.

So how doe I do number 2 and 3, thank you?

I have number 1:

GRANT ALTER ON SCHEMA::ca TO CCUser; -- OK

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: 1004514 More actions · Answer 5

For 2, try a google search for database permissions

For 3, try a google search for fixed server roles and database permissions

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 6

GilaMonster (9/29/2016)
For 2, try a google search for database permissions
For 3, try a google search for fixed server roles and database permissions

Thanks.

How about the following?

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,

UPDATE, VIEW DEFINITION ON SCHEMA::ca TO CCUser;

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 7

Eric M Russell (9/29/2016)
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.

Agreed. I was able GRANT them EXECUTE, SELECT, INSERT and DELETE Permissions.

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 8

GilaMonster (9/29/2016)
For 2, try a google search for database permissions
For 3, try a google search for fixed server roles and database permissions

I think you meant Fixed Database Roles? I am familiar with Fixed Server Roles and Database Fixed Server Roles.

I'm just familiar with how to restrict a user to their Schema.

I need to deny them from performing DDL operations on schema dbo.

I will continue to search Google but if someone know what the exact command are necessary to accomplish the objective feel free to share with me your knowledge.

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 9

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

Thanks for the link Phil.

It got me part was but no cigar.

I just want to grant the User permissions to create, alter and drop rights in their own schema but deny them fro making a changes in the dbo 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/

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

Good thing that I do not need this anymore.

Thank you to everyone that was helpful.

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/