Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You can define default Schema {dbo, for example } for domain users, but not for domain groups. –  user18493 Jan 28 '13 at 9:40
add comment

2 Answers 2

up vote 3 down vote accepted

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:

share|improve this answer
    
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 '11 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 '11 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 '11 at 20:40
    
Permissions will default to schema owner, which is user1. This is like DB_OWNER for schema –  gbn Nov 23 '11 at 20:49
add comment

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.

share|improve this answer
    
We wrote the app. –  flipdoubt Nov 23 '11 at 18:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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