SQL 2008 R2 tworzy użytkownika / schemat, gdy użytkownik systemu Windows tworzy tabele

Dodaliśmy login serwera i użytkownika bazy danych, który mapuje grupę Windows na instancję SQL 2008 R2 przy użyciu następującego skryptu, ze zmienionymi nazwami dla anonimowości:

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

Gdy konto DOMAIN \ Użytkownik1 loguje się do aplikacji, Użytkownik1 dobrze sprawdza zapytania w tabelach w schemacie dbo, ponieważ Użytkownik1 jest członkiem DOMAIN \ AppUsers, ale ta aplikacja umożliwia również tworzenie tabel. Podczas tworzenia tych tabel bez określania schematu SQL Server wykonuje następujące czynności:

1. Tworzy użytkownika „DOMAIN \ Użytkownik1” w AppDb, który korzysta z loginu „DOMAIN \ Użytkownik1” niewymienionego w SSMS \ Security \ Logins dla instancji.
2. Tworzy schemat „DOMAIN \ Użytkownik1” w AppDb.
3. Tworzy te tabele, używając nowego schematu „DOMAIN \ Użytkownik1”.

Jestem całkowicie zaskoczony tymi wynikami. Oto moje pytania:

1. Spodziewałbym się, że tworzenie tabeli zakończy się niepowodzeniem, niż tworzenie dodatkowych obiektów. Czy ktoś może wskazać mi część Books Online, która to wyjaśnia?
2. Dlaczego serwer nie tworzy schematu „DOMAIN \ AppUsers” i nie dodaje nowych tabel do tego schematu, jeśli zamierza dodać schematy?
3. Ponadto, w jaki sposób baza danych używa danych logowania niewidocznych w SSMS \ Security \ Logins?
4. Patrząc na użytkownika „DOMAIN \ Użytkownik1” w SSMS \ Databases \ AppDb \ Security \ Users, ikona użytkownika ma małą czerwoną strzałkę skierowaną w dół. Co to znaczy?

Właśnie zaczynamy używać Uwierzytelniania Windows w organizacji, która preferowała Uwierzytelnianie SQL dla uproszczenia, więc jestem pewien, że moje pytanie wynika z niewiedzy o różnicach. Ten kod został napisany na długo przed rozważeniem użycia uwierzytelniania systemu Windows, więc jestem pewien, że musimy lepiej zrozumieć tworzenie nowych schematów po zalogowaniu się przy użyciu uwierzytelniania systemu Windows, jak każdy inny niż właściciel bazy danych.

W przypadku, gdy nie możesz powiedzieć, to ja nalegam na użycie uwierzytelnienia systemu Windows zamiast uwierzytelnienia SQL. Jeśli nie zrozumiemy tego dobrze, wrócimy do uwierzytelniania SQL.

To się zawsze zdarzało, wracając do SQL Server 2000.
Skąd SQL Server wie, że chcesz umieścić go w dboschemacie?

Jedynym sposobem określenia domyślnego schematu jest:

• używaj loginów SQL (nie Windows)
• uruchom jako „sysadmin”

Żadne z nich nie jest dopuszczalne

Najlepszą praktyką jest zawsze kwalifikowanie schematu dla każdego odwołania do obiektu dla DDL i DML. Korzyści wynikające z ponownego wykorzystania planu są wyraźne.

Ponadto celowe użycie schematu jest lepsze dla SQL Server 2005:

• tabele w Data
• inne stoliki w Archive, Stagingetc
• Kod w schematach na uprawnieniach klienckich: Desktop, WebGUIetc

Używanie schematu dbo jest więc ostatnim Millenium :-) Linki:

• Projektowanie schematu - najlepsze praktyki?
• https://dba.stackexchange.com/q/7257/630

Cóż, @gbn pisze szybciej niż ja ...

Moja propozycja jest tylko ... się odwoływać użytkownik loguje się do aplikacji, a to pozwala użytkownikowi na tworzenie tabel i takie. Jeśli aplikacja na to pozwala, a użytkownik nie robi tego za pośrednictwem samego programu SQL Server (logowanie bezpośrednio do bazy danych za pomocą SSMS), należy skontaktować się z dostawcą tej aplikacji.

Chociaż pytanie jest bardzo stare i ma już zaakceptowaną odpowiedź, postaram się odpowiedzieć na twoje pytania bardziej szczegółowo (zamiast udzielać ogólnych porad).

1. Zachowanie jest udokumentowane w https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql , w sekcji „Schemat niejawny i tworzenie użytkownika”

2. Jeśli chcesz, aby obiekty były tworzone w określonym schemacie (gdy schemat nie jest wyraźnie określony w instrukcji), powinieneś określić DEFAULT_SCHEMA dla użytkownika. W SQL Server 2008 R2 (i wcześniejszych wersjach) wystąpiłby błąd przy próbie przypisania DEFAULT_SCHEMA do użytkownika opartego na grupie Windows, ale w SQL Server 2012 (i późniejszych wersjach) jest to teraz możliwe.

3. To nie jest pokazane tylko dlatego, że nie ma loginu dla tego użytkownika. Jak określono w https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql , użytkownik może zostać utworzony dla kogoś, kto loguje się przy użyciu innej grupy lub nawet bez dowolny login.

4. Mała czerwona strzałka (lub mała czerwona x w nowszych wersjach SSMS) reprezentuje użytkownika, który nie ma uprawnienia CONNECT w bazie danych (jednak to uprawnienie można uzyskać za pośrednictwem innej grupy).

Chociaż jest to stare pytanie, zauważyłem to zachowanie (w SQL Server 2014) i znalazłem następujące:

Biorąc pod uwagę scenariusz

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
)

Ten skrypt utworzy dwie tabele o nazwie Test.

Pierwszy CREATE TABLEtworzy tabelę o nazwie, [MyDomain\MyAdGroupUser].[Test]a także tworzy MyDomain\MyAdGroupUserschemat (a także MyDomain\MyAdGroupUserużytkownika bazy danych, który jest wyłączony)

drugi CREATE TABLEtworzy tabelę o nazwie[dbo].[Test]

Powodem tego jest to, że ponieważ CREATE TABLEpolecenia nie wyjaśniają schematu, użyją domyślnego schematu.

Ponieważ nie określiliśmy domyślnego schematu podczas tworzenia użytkowników, SQL Server ustawia domyślny schemat użytkownika Windows jako dbo, ale NIE ustawia ŻADNEGO domyślnego schematu dla użytkownika grupy Windows, a zatem powoduje to utworzenie schematu / użytkownika