Grant Control On Schema verses granting individual permissions on schema?

Question

Grant Control On Schema verses granting individual permissions on schema?

HookSQLDBA7

SSChampion

Points: 10709
More actions
June 5, 2013 at 2:39 pm

#278294

Would granting control at the schema level take the place of granting individual permissions on the schema? For example, GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
Verses
--apply permissions to schemas
GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT INSERT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT DELETE ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT UPDATE ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
--ensure role membership is correct
EXEC sp_addrolemember N'Developer_Role ', N'User1'
GO
--allow users to create tables in Developer_Schema
GRANT CREATE TABLE TO [Developer_Role]
GO
--Allow user to connect to database
GRANT CONNECT TO [User1]
Thanks, Kevin

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 1

BOL -

Object owners can grant permissions on the objects they own. Principals with CONTROL permission on a securable can grant permission on that securable.

Grantees of CONTROL permission on a schema can grant any permission on any object within the schema.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 2

Thanks Ness. So, I guess "Principals with CONTROL permission on a securable can grant permission on that securable.

Grantees of CONTROL permission on a schema can grant any permission on any object within the schema" means that principals can ONLY grant permissions on securables but not access the securable, for example, with a Select, Insert, Delete or Update Statement. Hence, the reason for having to apply the individual permissions for Select, Insert, Delete and Update Statements:

GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

GRANT INSERT ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

GRANT DELETE ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

GRANT UPDATE ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GO

I will have to read up more on this topic.

Thanks, Kevin

vvk Newbie Points: 1 More actions · Answer 3

Hi all,

We have an issue on a database that any newly created Login/its User gets automatically below permissions, sees a lot more than what the objects in the Public role added...

I created a new user for this test with in [MyDB] and it automatically sees /have all below permissions when no explicit permissions granted for all below /no roles to the user 'testtest'' (except that it is a member of the Public role which has 14 user objects permissions in it)?

Can this be reverted ?

Any help would be appreciated here.

GRANT CONTROL