10

We added a server login and database user that map a Windows Group to a SQL 2008 R2 instance using the following script, with the names changed for anonymity:

USE master
go
CREATE LOGIN [DOMAIN\AppUsers] FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
USE AppDb
go
CREATE USER [DOMAIN\AppUsers] FOR LOGIN 
[DOMAIN\AppUsers]
go
EXEC sp_addrolemember N'db_owner', N'DOMAIN\AppUsers'
go

When the DOMAIN\User1 account logs onto the app, User1 queries tables in the dbo schema just fine because User1 is a member of DOMAIN\AppUsers, but this app allows the user to create tables too. When creating these tables without specifying a schema, SQL Server does the following:

  1. Creates a 'DOMAIN\User1' user in AppDb that uses a 'DOMAIN\User1' login not listed in SSMS\Security\Logins for the instance.
  2. Creates a 'DOMAIN\User1' schema in AppDb.
  3. Creates those tables using in the new 'DOMAIN\User1' schema.

I am completely baffled by these results. Here are my questions:

  1. I would expect the table creation to fail rather than create additional objects. Can someone point me to the part of Books Online that explains this?
  2. Why doesn't the server create a 'DOMAIN\AppUsers' schema and add the new tables to that schema if it is going to add schemas?
  3. Also, how does the database use a login not shown in SSMS\Security\Logins?
  4. Looking at the 'DOMAIN\User1' user in SSMS\Databases\AppDb\Security\Users, the user icon has a small red arrow pointing down. What does that mean?

We are just starting to use Windows Authentication within an organization that preferred SQL Authentication for simplicity, so I am sure my question comes from being ignorant of the differences. This code was written long before we considered using Windows Authentication, so I sure we need to improve our understanding of creating new schemas when logged on using Windows Authentication as anyone other than the database owner.

In case you can't tell, I am the one pushing for the use of Windows Authentication over SQL Authentication. If we don't get to a solid understanding of this, we will revert back to SQL Authentication.

1
  • You can define default Schema {dbo, for example } for domain users, but not for domain groups.
    – user18493
    Jan 28, 2013 at 9:40

4 Answers 4

9

This has always happened, back to SQL Server 2000.
Without schema, how does SQL Server know you want to put it in the dbo schema?

The only way to specify a default schema is to :

  • use SQL logins (not Windows)
  • run as "sysadmin"

Neither of these is acceptable

Best practice is to always qualify schema for every object reference for DDL and DML. There are clear performance benefits because of plan re-use.

Also, deliberate schema use is better for SQL Server 2005:

  • tables in Data
  • other tables in Archive, Staging etc
  • code in schemas per client permissions: Desktop, WebGUI etc

Using the dbo schema is so last millenium :-) Links:

4
  • So my take away from this is that we should update the code to prefix new tables with the schema, right? Does this mean it is a common occurrence to have new user's appear under the Security node?
    – flipdoubt
    Nov 23, 2011 at 19:24
  • @flipdoubt: yes to both. Once you get into it, and use schemas as namespaces or containers, it becomes second nature
    – gbn
    Nov 23, 2011 at 19:34
  • One last question. If it is common practice to have users automatically added and a best practice to specify schemas during object creation, how can you grant rights to the new schema before the automatically created user creates an error querying an object in the new schema? Using the example I posted regarding 'DOMAIN\User1', I can assign access to the 'DOMAIN\AppUsers' account, but will queries use the access rights for 'DOMAIN\User1' or 'DOMAIN\AppUsers'? It is just so sneaky that the server creates the user as soon as the object is created.
    – flipdoubt
    Nov 23, 2011 at 20:40
  • Permissions will default to schema owner, which is user1. This is like DB_OWNER for schema
    – gbn
    Nov 23, 2011 at 20:49
3

Although the question is very old and it already has an accepted answer, I will try to answer your questions more specifically (rather than giving general advice).

  1. The behaviour is documented in https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql, in the section "Implicit Schema and User Creation"

  2. If you want the objects to be created in a particular schema (when the schema is not specified explicitly in the statement), you should specify the DEFAULT_SCHEMA for the user. In SQL Server 2008 R2 (and earlier versions), you would get an error if you try to assign a DEFAULT_SCHEMA to a user based on a Windows group, but in SQL Server 2012 (and later versions) this is now possible.

  3. It's not shown simply because there is no login for that user. As specified in https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql, a user may be created for someone who logs in using a different group or even without any login.

  4. The small red arrow (or the small red x in the newer versions of SSMS) represents a user who does not have the CONNECT permission in the database (however, this permission may be obtained through another group).

2

Well @gbn types faster than I do...

My only offering is...You reference the user is logging into the app and it allows the user to create tables and such. If the application is allowing this and the user is not doing this through SQL Server itself (logging directly into the database using SSMS) then you will need to check with the vendor of that application.

1
  • We wrote the app.
    – flipdoubt
    Nov 23, 2011 at 18:34
0

Whilst this is an old question I have observed this behaviour (In SQL Server 2014) and found the following:

Given the script

USE [master]
CREATE DATABASE [Test]

USE [Test]

-- Note that we create the users without specifying the default schema
CREATE USER [MyDomain\MyADGroup] FOR LOGIN [MyDomain\MyADGroup] -- ad group
CREATE USER [MyDomain\MyDomainUser] FOR LOGIN [MyDomain\MyDomainUser] -- ad user (not in said AD group)

ALTER ROLE db_ddladmin ADD MEMBER [MyDomain\MyADGroup]
ALTER ROLE db_ddladmin ADD MEMBER [MyDomain\MyDomainUser]

EXECUTE AS LOGIN = 'MyDomain\MyAdGroupUser' -- a windows account which is a member of [MyDomain\MyADGroup]

CREATE TABLE Test
(
    a INT
)

REVERT 

EXECUTE AS USER = 'MyDomain\MyDomainUser'

CREATE TABLE Test
(
    a INT
)

This script will create two tables called Test.

The first CREATE TABLE creates a table called [MyDomain\MyAdGroupUser].[Test] and also creates the MyDomain\MyAdGroupUser schema (as well as a MyDomain\MyAdGroupUser database user which is disabled)

the second CREATE TABLE creates a table called [dbo].[Test]

The reason for this is that as the CREATE TABLE commands do not explicity state the schema, they will use the default schema.

As we did not specify the default schema when creating the users, SQL Server sets the Windows user's default schema as dbo but does NOT set ANY default schema for the Windows Group user and therefore this causes the schema / user creation

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.