November 15, 2011 at 2:05 pm
Hello,
I have created a schema called 'hr' and need to have an active directory group be able to create tables truncate, select, delete, and update tables in this schema only and not DBO. What would be the appropriate permissions? I set up a role to grant permissions to. When I use a local account as a member of the role it work fine, but using AD account they can create object as DBO. What am I missing?
Thanks.
November 15, 2011 at 2:36 pm
Does that AD account already have other permissions in the database under the dbo schema?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 15, 2011 at 3:01 pm
Just data reader. I can always drop it and add it back.
November 16, 2011 at 2:20 am
Why don't you explicitly DENY the permissions on [dbo] schema for that AD Group.
November 16, 2011 at 5:51 am
Usman Butt (11/16/2011)
Why don't you explicitly DENY the permissions on [dbo] schema for that AD Group.
What happens should a user be part of the AD group and also part of another group which needs access to the DBO schema, the DENY will override the GRANT (unless your in SSAS where GRANT overrides DENY)
November 16, 2011 at 5:54 am
They can still create under DBO as you cannot specify a default schema for an AD group, so it will always default to DBO
One way to get around this is to implicity define the schema name, other than that, DENY the create but as I have said above, should someone in the AD group need to create objects in the DBO schema, DENY will stop them
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply