Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Table designer using new DOMAIN\User schema instead of dbo when creating new tables Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 12:41 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:03 AM
Points: 490, Visits: 937
I have recently created 2 new SQL 2008 instances. One on a new VM and the other on an existing VM (this one is a named instance as the VM already had a default SQL instance).

I have created a SQL login which is mapped to a Windows Group in AD. This login has a Server Role of Public and a User Mapping to a database. I have assigned Database Roles of public, db_datareader and db_datawriter to this User. In the Database Permissions I have granted Alter, Connect, and Execute permissions and Denied Delete permission.

I have 3 members in the Windows Group. 2 of these members, when they create a new table in SSMS Table Designer, the table is given the dbo schema. When the other member creates a table, the table is given a schema of [Domain\User].

I have the same setup in the SQL instance on the new VM and all 3 users can create tables with dbo schema.

As I am using a Windows Group (and the fact you cannot assign default schemas to Windows Groups), I've ruled that out as the problem. And as it works correctly on the new VM (with the same user), that rules out a Windows User problem. The only difference is that the SQL instance is on a different VM and the fact that one is a default instance and the other is a named instance. Why this would make a difference though, I have no idea, and more importantly I'm not sure what I can do to correct it?

Has anyone else come across this issue, or can anyone recommend any steps to try resolve it?

[Edit] If I create a specific SQL login for the user I'm having problems with and give them db_owner role, the problem is corrected. But why would it work for the other 2 users that I don't give db_owner role to? Another thing to mention (not sure if it makes a difference) is that I am using Windows Authentication with Kerberos.
Post #1362912
Posted Friday, September 21, 2012 1:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
Check that the two users that get their tables created in dbo are not somehow in the sysadmin Fixed Server Role, either explicitly by a named login (but you said you didn't create them that way) or via another group, e.g. BUILTIN\Administrators (hopefully you do not have this group since its a new 2008 install) or some other group.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1362930
Posted Friday, September 21, 2012 2:57 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:03 AM
Points: 490, Visits: 937
Yep, there are no named logins, only the Windows Group, and that Group is not a member of sysadmin. All 3 users in that group are also Domain Admins and BuiltIn\Administrators in AD.
Post #1363004
Posted Friday, September 21, 2012 3:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
Are there any diffs between the privilege or permission_path when you run this against each principal?

EXEC sys.xp_logininfo 
@acctname = 'domain\user1',
@option = 'all';
EXEC sys.xp_logininfo
@acctname = 'domain\user2',
@option = 'all';
EXEC sys.xp_logininfo
@acctname = 'domain\user3',
@option = 'all';
EXEC sys.xp_logininfo
@acctname = 'domain\group',
@option = 'all';



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363008
Posted Friday, September 21, 2012 4:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:03 AM
Points: 490, Visits: 937
Results from the above on the named instance (the one that doesn't work as it should) are:

account name     type      privilege     mapped login name     permission path
------------------------------------------------------------------------------
domain\user1 user user domain\user1 domain\group
domain\user2 user user domain\user2 domain\group
domain\user3 user user domain\user3 domain\group
domain\group group user domain\group

Where user3 is the one who uses the domain\user3 as the schema when creating new tables.

This is also the one which I created the specific SQL login with db_owner role. When I did this there was another entry for domain\user3

account name     type      privilege     mapped login name     permission path
------------------------------------------------------------------------------
domain\user3 user user domain\user3 NULL

Users 1 and 2 can both create new tables using the dbo schema by default.

Whereas on the default instance it is:

account name     type      privilege     mapped login name     permission path
------------------------------------------------------------------------------
domain\user1 user user domain\user1 domain\group
domain\user2 user user domain\user2 domain\group
domain\user3 user user domain\user3 domain\group
domain\group group user domain\group

And domain\user3 can create tables under dbo schema.
Post #1363032
Posted Friday, September 21, 2012 4:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
Forgive me if we're covering already covered ground. I am trying to follow along.

Is it to say that you created a new login on the named instance for domain\user3? Has that user since been dropped? Is there still a schema named domain\user3 in place?

USE YourDatabase;
GO
SELECT *
FROM sys.schemas
WHERE name = N'domain\user3';

What does this say, if anything?

USE YourDatabase;
GO
SELECT sp.name,
dp.default_schema_name
FROM sys.server_principals sp
JOIN sys.database_principals dp ON sp.sid = dp.sid
WHERE sp.name = N'domain\user3';

edit:

PS meant to say the permissions paths all look as I would expect so that is good


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363034
Posted Friday, September 21, 2012 7:56 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:03 AM
Points: 490, Visits: 937
I'm more than happy to provide as much detail as I can, if it helps to get a resolution to the problem.

Yes I created a new login on the named instance. The server principal that I created has since been deleted, along with the database principal and database schema that was also created for this user. The result was the same upon trying to create a new table.

I've also created a copy of the problem user in AD and assigned them to the same Windows Groups. This copy has no problems creating tables. Taking all this into account it appears to lead to the problem being with the specific domain\user3 in the specific named instance only.

Knowing where to look next to try find where the problem lies is the difficult part
Post #1363054
Posted Friday, September 21, 2012 9:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
I am running out of ideas so I re-read the whole thread. Something says to me this is related to Windows Group membership and possibly membership in multiple groups.

I know you said their all Domain Admins which is fine, but are all three users in the same set of AD groups? Are they in any local Windows Groups on the problem instance?

If there are any diffs in their memberships trace those through to the SQL Server to ensure User3 does not have a different set of paths to enter the SQL Server, and potentially the database in question.

Also, SQL Server caches a lot of security info as it relates to AD. It might be worth cycling the server with the problem to ensure a clean slate there.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363059
Posted Friday, September 21, 2012 9:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:03 AM
Points: 490, Visits: 937
Hmmm think I have made some progress here...

I went back to the copy of the domain\user3 account and logged into the named instance. I then went to create a new table in the designer, and as it did before, it used the dbo schema. OK, so then I issued the following T-SQL statement:

CREATE TABLE TestTable (ID INT NULL)

This then created a table with the schema domain\user3copy, and also created the database user and schema with that name. Then I went to create another table with the designer and this time it used the domain\user3copy schema.

If I now delete the new table and domain\user3copy database user and schema that was just created and then create a new table in the designer it uses the dbo schema. However, if I try to change the table in the designer (once I have saved it) it won't let me and tells me the table is read only.

So does this mean I have to make the Windows Group a member of db_owner? I don't want to do this because it would give the users more privileges than I want to allow. Such as drop database!
Post #1363060
Posted Friday, September 21, 2012 10:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
This describes what you're running into:

Connect > Default Schema for Windows Group


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1363066
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse