Grant Control On Schema verses granting individual permissions on schema?

  • 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

  • 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

  • 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

  • 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

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

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