Assigning default schema for an entire instance

SQL Server Administration
1

I know how to change the default_schema for a database but not for the entire instance.

Is it possible or do I need to change it for each database separately?

Currently I have one user that would like to have the default of dbo, however I would like this to be in effect for a windows group.

2

sure, default_schema only makes sense at the db level anyway. Just edit the properties of the user (which is your windows group) in the db you want and change the default_schema there.

3

Thanks, I was afraid of that. I have about 16 databases and four users in our development team (this is the dev system) that if I could change the group I would have less to do.

4

You can. That's what I meant. In the 16 databases, define the Windows group as a user, not the members of the group, Then change the default schema of that user

5

Tried that before the post. "The DEFAULT_SCHEMA clause cannot be used with a Windows group or with prinipals mapped to certificates or asymmetric keys. (Microsoft SQL Server, Error: 15259)" :disappointed:

6

I did not know that

7

Neither did I :neutral_face:

8

I just did this in 2014:

alter user [groupname] with default_schema=[some schema]

It worked

furthermore, the gui shows default schema as an option I can set

This also worked:

create user [admin group] for login [builtin\administrators] with default_schema=[guest]
9

Well that is nice, but still in 2008R2 so no luck with default_schema on the create user login function. I tried that one also this morning. :weary:

10

To be able to that you need to upgrade to 2012 or higher. In 2008 R2 you have to create each individual user in each database to define a default schema for that user.

11

You can use system stored procedure 'sp_MSForeachdb'.
like this:

exec sp_MSForeachdb 'if db_id(''?'') > 4
use [?]
EXEC sp_addrolemember N''db_owner'', N''WinDomainName\WinDomainUserName'''