5

I have created a schema. I need this schema to be accessible to all currently users and new users that will be created in the future. The only queries i find on the internet is this GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [MySchema] TO [userhere]

1
  • It would be helpful to mention the database management system (server) being used. Apr 3 at 11:28

1 Answer 1

5

to be accessible to all currently users and new users that will be created in the future

You should give the desired access to public role:

INSERT, UPDATE, DELETE ON SCHEMA :: [MySchema] TO public
11
  • thanks, i cant accept this as an answer since i have less than 15 reputation.
    – Nelani
    Apr 2 at 14:02
  • 1
    @Nelani You might not be able to upvote, but there's no limits for accepting the answer (clicking the checkmark). Note also, it is possible for someone to have access to a database instance without being in the public role. They would be missed in such a case. But it's a very unlikely scenario since the default for new users is to be in the public role.
    – J.D.
    Apr 2 at 14:04
  • @J.D. can you please provide any example of how to drop user from public role?
    – sepupic
    Apr 2 at 14:33
  • @sepupic You can't drop a user from the public role of a database they're mapped to. But a Login that can access the database instance, doesn't have to be mapped to the database to begin with. That's probably clearer terminology than what I previously stated. Also, a Login can be unmapped but still access said database even, should they be in the SysAdmin server role. But that would be a moot point here since they would then implicitly have access to the user created schema anyway.
    – J.D.
    Apr 2 at 14:58
  • @J.D. Do you want to say that any member of sysadmin server role CAN not be a menber of public database role? I insist that it will be, and it cannot be changed
    – sepupic
    Apr 2 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.