January 5, 2011 at 6:10 pm
We are using SS 2008 (not R2) on 2K3.
My boss requests the following scenario:
userA userB
roleA roleB
schemaA schemaB
userA should have full access to schemaA via role roleA (user not directly associated with schemaA) (same goes for items of group B).
userA should be able to perform (delete, execute, insert, references, select , update, view change tracking, view definition) on objects (1->N) in schemaB (vice versa, B->A).
userA should NOT be able to perform (alter, control, take ownership) on objects (1->N) in schemaB (vice versa, B->A).
I’ve tried explicit Grant & Deny at the user, role and schema level with role and schema owners set both as userA and dbo (and userB and dbo).
When exercising CRUD & DDL from one schema to another I either get all or nothing, namely userA can do nothing in userB’s schema or userA can do everything in userB’s schema (remember, schema is associated to user via role (and I’ve shortcut the role for just user(C) and schema but I still cannot get the desired behaviour)).
Essentially we are going for user/role/schema isolation to prevent cross schema DDL without going down to the object level (as there (are/will be) 1->N objects).
Is this even possible or have I been missing the boat (for more than) the whole day?
Thank you in advance.
January 5, 2011 at 9:18 pm
Follow-up: per articles such as this ("SQL Server Best Practices – Implementation of Database Object Schemas" http://msdn.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx ) it does indeed seem possible, per the documentation and example, but none of the changes (to users/roles/schemas) persist. This is absolutely maddening!
January 7, 2011 at 2:14 pm
jkelly (1/5/2011)
Follow-up: per articles such as this ("SQL Server Best Practices – Implementation of Database Object Schemas" http://msdn.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx ) it does indeed seem possible, per the documentation and example, but none of the changes (to users/roles/schemas) persist. This is absolutely maddening!
This works for me in AdventureWorks database.
create role SalesAssociate;
grant select on schema::Sales to SalesAssociate;
exec sp_addrolemember 'SalesAssociate', 'guest';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 7, 2011 at 2:23 pm
When I want that kind of security isolation, I separate by database, not by schema. Much easier to manage.
I'd have to see samples of the scripts you're using for the grant and deny actions to be able to begin to help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2011 at 5:23 pm
Thanks Eric, I kept @ it.
@ GSquared: agree but something about management decisions ...
Solution: (my own ignorance ... )
When setting permissions on, say a role or a schema, if you deny “Control” to the (user or role) it will override any other settings that you have selected, even explicit grants.
Evaluating “Deny” first makes sense, the real shocker was how powerful “Control” is – it supersedes (Delete, Execute, Insert, References, Select, Update, View Change Tracking, View Definition, Alter, and Take Ownership).
Hint: http://www.exforsys.com/tutorials/sql-server-2005/sql-server-permissions.html
"... CONTROL gives all permissions to the object owner... "
What's a few more gray hairs ...
Thanks all - J
January 13, 2011 at 7:14 am
Makes sense. Glad you got it resolved.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply