January 1, 2012 at 1:31 pm
How do i disable ownership chaining in sql server?
I know i can do this for cross database ownership chaining using sp_configure, but i cant find an option to ensure that permissions are checked for each object during execution even when not going across databases.
January 1, 2012 at 2:07 pm
There's no such option.
If you want to break ownership chaining, you'll have to make sure that the various objects are owned by different database users. (not necessarily created by, and not in different schemas)
Why do you want to do this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 1, 2012 at 2:11 pm
I have no specific need right now, but thought it should be possible. If you had a loooong chain of objects, and you want to ensure permissions are explicitly checked each time, it may be useful.
January 1, 2012 at 3:22 pm
It's possible, but it's not easy. You'd need to mess with object ownership
The other way is dynamic SQL. That requires explicit permissions on the objects referred within (which is one reason why dynamic SQL is a pain)
Generally you don't want a long chain of objects. It's possible you could have that and have a good DB design and coding structure, but it's unlikely. Code reuse in databases (which is generally why people do the chain of objects) doesn't work well in many, many cases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 1, 2012 at 11:36 pm
GilaMonster (1/1/2012)
It's possible, but it's not easy. You'd need to mess with object ownershipThe other way is dynamic SQL. That requires explicit permissions on the objects referred within (which is one reason why dynamic SQL is a pain)
Generally you don't want a long chain of objects. It's possible you could have that and have a good DB design and coding structure, but it's unlikely. Code reuse in databases (which is generally why people do the chain of objects) doesn't work well in many, many cases.
+1 for good DB design.
As Gail has already mentioned, there are certain advantages (& disadvantages) of Ownership Chains & that’s why its default behaviour set in SQL Server. The easiest way to break it is to set the individual owners of the objects & give limited permissions on individual objects. It’s painful but possible.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply