April 24, 2012 at 12:29 pm
Hello, I have a situation in where I have one db user (let's call it webuser) that has access to multiple schemas in a database. So far everytime we created a schema, we've been granting this user access to it. However all of the schemas that webuser accesses, has the same owner (webobjects). Is there a simplier way we can set permissions so that webuser can access anything owned by webobjects (without using EXECUTE AS or IMPERSONATE everytime)
April 24, 2012 at 1:17 pm
Gabriel P (4/24/2012)
Hello, I have a situation in where I have one db user (let's call it webuser) that has access to multiple schemas in a database. So far everytime we created a schema, we've been granting this user access to it. However all of the schemas that webuser accesses, has the same owner (webobjects). Is there a simplier way we can set permissions so that webuser can access anything owned by webobjects (without using EXECUTE AS or IMPERSONATE everytime)
Something is not computing...if webuser has been granted the necessary access to each schema (is it exec on procs, select on tables, need more info...) then what is the purpose of using impersonation?
What purpose is webobjects serving? Is having webuser own the schema an option? That would alleviate the need for all grants on those schemas.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 1:27 pm
You're saying through ownership chaining the user has appropriate access.
As far as tying to a particular owner, no.
When you create a schema you should create the permissions then. While this won't directly grant the permission based on who the owner of the schema is, that's the right level and the right time to assign the permissions. You should be able to script this.
If that doesn't work for you, then you can simply build a SQL Agent job that queries for objects/schema owned by a particular owner which builds and executes the T-SQL commands to give the user the permissions needed.
K. Brian Kelley
@kbriankelley
April 24, 2012 at 2:44 pm
K. Brian Kelley (4/24/2012)
You're saying through ownership chaining the user has appropriate access.As far as tying to a particular owner, no.
When you create a schema you should create the permissions then. While this won't directly grant the permission based on who the owner of the schema is, that's the right level and the right time to assign the permissions. You should be able to script this.
If that doesn't work for you, then you can simply build a SQL Agent job that queries for objects/schema owned by a particular owner which builds and executes the T-SQL commands to give the user the permissions needed.
Thanks for the insight. Will go this route.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply