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:
- Creates a 'DOMAIN\User1' user in AppDb that uses a 'DOMAIN\User1' login not listed in SSMS\Security\Logins for the instance.
- Creates a 'DOMAIN\User1' schema in AppDb.
- Creates those tables using in the new 'DOMAIN\User1' schema.
I am completely baffled by these results. Here are my questions:
- 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?
- 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?
- Also, how does the database use a login not shown in SSMS\Security\Logins?
- 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.