Post reply

Grant Control On Schema verses granting individual permissions on schema?

  • June 10, 2013 at 9:58 am

    #1622510

    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

  • June 10, 2013 at 12:45 pm

    #1622582

    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

  • March 28, 2022 at 1:37 pm

    #4007090

    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 3 posts - 1 through 4 (of 4 total)

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