2 thoughts on “How to Grant limited permissions to create views in another schema?

  1. I wasn’t sure from your post what type of user or environment this was modeled for, but I disagree that this provides “limited” permissions as ALTER is not a “limited” permission in any way; it is very much an elevated permission. Granting ALTER at the schema level is very, very risky because it allows the user to alter schema-owned objects in that schema. This would also be a red flag to an auditor (and a finding on an audit) as it adds a significant amount of risk to the integrity and availability of the asset, e.g., the data. I can’t think of a scenario in a production system housing valuable data where anyone other than the DBA should have the ability to ALTER database objects. If there is such a scenario where an auditor (internal or external) would allow this in an production system please let me know.

    In a development environment – maybe; in a production environment – no.

    Granting this permission at the schema level can also provide unintended access to the user (https://msdn.microsoft.com/en-us/library/ms187940.aspx). Per the link: “A user with ALTER permission on a schema can use ownership chaining to access securables in other schemas, including securables to which that user is explicitly denied access. This is because ownership chaining bypasses permissions checks on referenced objects when they are owned by the principal that owns the objects that refer to them. A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema’s owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema’s owner. When possible, you should avoid granting ALTER permission on a schema if the schema’s owner also owns other schemas.”

    Like

    • hi .. at the start i have mentioned that u can use different schema. and u can list down the object under that schema and give the permission on that schema .

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s