June 21, 2006 at 6:57 am
Hi,
When we create a user in an SQL2005 database we can specify the default schema this user need to use.
ex: create user [xxxx] from login [xxxx] with default_schema = abc_schema
I know that with the current version we can't assign a default schema to a group user
ex: create user [nt_group] from login [domain\Nt_group] with default_schema = abc_schema
This will fail.
what we do now is we create every group memeber in the database and we assign a default_schema to it.
This however,isn't maintenance frendly because:
1° we need to create all users from this group in the database and
2° when a user leaves the group and goes to another domain group, we need to remember that we have to delete this user from the database.
Do someone have a better solution for this .
Somthing that assigns automaticly a schema to a user that connect to a database by group membership.
Greatings
Ludo
June 21, 2006 at 10:59 am
Create a database role, set up the permissions at the role level, and assign the group to the database role. I believe that will accomplish what you are trying to do?
jg
June 23, 2006 at 10:25 am
You may want to read the following discussion on the subject, that talks about if a Windows login could be a member of more then 1 group with different default schemas in case a default schema for the Windows group would be supported: "The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one."
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408092&SiteID=1
MSDN Forums » SQL Server » SQL Server Security » Re: Unable to set Default Schema for a group
You may want to read the whole thread.
Regards,Yelena Varsha
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply