September 21, 2012 at 12:41 pm
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.
September 21, 2012 at 1:02 pm
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
September 21, 2012 at 2:57 pm
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.
September 21, 2012 at 3:04 pm
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
September 21, 2012 at 4:25 pm
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.
September 21, 2012 at 4:46 pm
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
September 21, 2012 at 7:56 pm
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 :hehe:
September 21, 2012 at 9:22 pm
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
September 21, 2012 at 9:31 pm
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!
September 21, 2012 at 10:13 pm
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
September 23, 2012 at 10:47 am
Yeah this looks like the problem. Apparently fixed in SQL 2012, but no mention of hot fixes for 2008.
Anyway it looks like the problem with it defaulting to the domain\user3 schema may have been because that schema had already been created previously (by a CREATE TABLE without specifying the dbo schema). I will delete the domain\user3 and database user for the user I was having problems with tomorrow and see if that fixes it in the designer.
For the problem of allowing a user to change a table in the dbo schema without having them be a member of db_owner, I solved this by giving the Windows Group VIEW DEFINITION permissions on the dbo schema and ALTER permission on the database.
Thanks for all your help and suggestions on this one. Much appreciated.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply