September 6, 2016 at 1:40 pm
We have a group that when they have SA, it creates all tables as dbo.table. This makes sense since SA makes them default to dbo. When we remove SA, they make all tables as [domain/user].table. Even when I remove the table, schema, and user out of security for that database... it comes back. It's mind racking. If I define DBO under the user mapping, they all work fine. If I leave it undefined, it defaults to domain/user.
What am I missing in this search? is there another place to set a variable schema?
SQL Server 2014 - Windows auth - very confused.
.
September 6, 2016 at 1:49 pm
Bill (DBAOnTheGo) (9/6/2016)
We have a group that when they have SA, it creates all tables as dbo.table. This makes sense since SA makes them default to dbo. When we remove SA, they make all tables as [domain/user].table. Even when I remove the table, schema, and user out of security for that database... it comes back. It's mind racking. If I define DBO under the user mapping, they all work fine. If I leave it undefined, it defaults to domain/user.What am I missing in this search? is there another place to set a variable schema?
SQL Server 2014 - Windows auth - very confused.
Sounds like standard behaviour. What do you mean by this, please?
is there another place to set a variable schema?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2016 at 2:37 pm
Every user defaulting to [domain/user].tablename is standard behavior?
.
September 6, 2016 at 3:05 pm
Sure, if they aren't set to use dbo as the default schema in the database this would make sense.
Folks should always properly schema qualify their objects, this would help get everyone in the habit of that (or, of course, change their default schema)
September 6, 2016 at 3:11 pm
What is the compatibility level of the database where this happens? Any chance this is an upgrade from SQL 2008?
Sue
September 6, 2016 at 3:15 pm
You need to set the user's default schema.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 6, 2016 at 3:35 pm
The compatibility level is 120. I've never run into a server not defaulting to dbo. I think that's where i'm finding my confusion.
.
September 6, 2016 at 3:42 pm
Always worth, when creating users to include the default schema...
CREATE USER [Domain\User] FROM LOGIN [Domain\User] WITH DEFAULT_SCHEMA = dbo;
Fix with
ALTER USER [Domain\User] WITH DEFAULT_SCHEMA = dbo;
*Fix spelling of alter
September 6, 2016 at 3:43 pm
The behavior you're seeing is by design, and is described here: https://support.microsoft.com/en-us/kb/918346
While it only specifies 2005 and 2008 in the kb, it applies in 2014 as well under the conditions listed there:
1)The individual user belongs to one of the following sets of users:
....1a) Windows domain users
....1b) A local Windows group
2)Permissions have been assigned to the Windows domain or to the local Windows group.
3)Permissions have not been assigned to the account of the individual user.
Cheers!
September 6, 2016 at 4:25 pm
Bill (DBAOnTheGo) (9/6/2016)
The compatibility level is 120. I've never run into a server not defaulting to dbo. I think that's where i'm finding my confusion.
I think it's just the difference between users and groups. Initially in SQL 2005 when schemas were focused on more, users had a default schema and you could also assign a schema for a user but you could not assign a schema to a Windows group. So users in this group had no default schema. In SQL 2012, you could finally assign a default schema to a group. But you can still have a group without a default schema.
In that case, since it is a group and not a user, the default schema for the user in that group will be the login - Domain\Login. It doesn't default to dbo.
Members of sysadmins will default to dbo.
Hope that makes some sense but I think it's mostly the whole users vs Windows group that caused the confusion. And then the old group sounds like it was in sysadmins.
Sue
September 7, 2016 at 8:42 am
Thank you all for the help.
I think this explains some of the issues I'm seeing. I can work with this information.
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply