Loginy, użytkownicy i role w SQL Serverze: kto ma dostęp do czego

Baza CrmDB zostaje odtworzona z kopii na serwerze srv-sql-02, bo srv-sql-01 czeka na wymianę dysków. Administrator zakłada na nowej instancji login SQL crm_app z hasłem z pliku konfiguracyjnego aplikacji, a mimo to aplikacja się nie łączy. W dzienniku SQL Servera pojawia się wpis o nieudanym logowaniu, a próba przypisania loginu do bazy w SSMS kończy się błędem 15023:

Login failed for user 'crm_app'. Reason: Failed to open the explicitly specified database 'CrmDB'. [CLIENT: 10.10.30.25]
User, group, or role 'crm_app' already exists in the current database. (Microsoft SQL Server, Error: 15023)

Użytkownik w bazie i login na serwerze mają tę samą nazwę, ale SQL Server wiąże je przez identyfikator SID, a nie przez nazwę. Nowy login dostał nowy SID, więc użytkownik w odtworzonej bazie został bez loginu. Ten wpis porządkuje trzy poziomy dostępu: login, użytkownika i rolę, tak żeby przeniesienie bazy nie wymagało naprawiania kont.

Login jest obiektem instancji. Przechowuje go baza master, widać go w sys.server_principals i decyduje on o tym, czy SQL Server przyjmie połączenie. Login Windows odpowiada kontu albo grupie z katalogu i nie ma hasła w SQL Serverze, bo tożsamość potwierdza Windows. Login SQL ma nazwę i skrót hasła zapisany w master.

Użytkownik jest obiektem bazy. Widać go w sys.database_principals; to jemu nadaje się uprawnienia i role. Jest zapisany w samej bazie, więc trafia do kopii zapasowej i wraca przy odtworzeniu. Z loginem łączy go SID zapisany w obu miejscach. Login Windows ma SID konta z Active Directory, taki sam na każdym serwerze w domenie. Login SQL dostaje SID przy CREATE LOGIN, więc ten sam login założony na dwóch serwerach ma dwa różne identyfikatory.

-- Poziom instancji: loginy dla grup z Active Directory
CREATE LOGIN [CORP\GG-SQL-CRM-Read] FROM WINDOWS;
CREATE LOGIN [CORP\GG-SQL-CRM-Write] FROM WINDOWS;

-- Poziom bazy: użytkownicy powiązani z loginami przez SID
USE CrmDB;
CREATE USER [CORP\GG-SQL-CRM-Read] FOR LOGIN [CORP\GG-SQL-CRM-Read];
CREATE USER [CORP\GG-SQL-CRM-Write] FOR LOGIN [CORP\GG-SQL-CRM-Write];

Nowy login ma tylko uprawnienie CONNECT SQL i to, co daje rola public. Pozwala połączyć się z instancją, ale do bazy CrmDB wpuszcza dopiero użytkownik.

Grupy katalogu zamiast kont osobowych

Loginy dla pojedynczych osób tworzą listę, której nikt nie potrafi zweryfikować: konta po byłych pracownikach i uprawnienia nadane „na chwilę”. Lepszy układ to loginy wyłącznie dla grup, na przykład GG-SQL-CRM-Read dla osób, które czytają dane CRM, i GG-SQL-CRM-Write dla tych, które je zmieniają. Nadanie dostępu to dodanie osoby do grupy w Active Directory, a wyłączenie konta po odejściu pracownika zamyka też dostęp do SQL Servera.

Loginu grupy nie da się wyłączyć poleceniem ALTER LOGIN … DISABLE, które kończy się błędem 15151. Grupę blokuje się uprawnieniem: DENY CONNECT SQL TO [CORP\GG-SQL-CRM-Read].

Role serwera: sysadmin i jej odpowiedniki

Rola sysadmin pozwala na wszystko, a odmowy (DENY) jej członków nie dotyczą. Instalator dodaje do niej konto sa, identyfikatory usług instancji (NT SERVICE\MSSQL$CRM, NT SERVICE\SQLAgent$CRM), NT SERVICE\SQLWriter i NT SERVICE\Winmgmt oraz konta wskazane podczas instalacji. Wpisów usług nie należy usuwać. Poza nimi w roli powinna być jedna grupa: GG-SQL-Admins.

CREATE LOGIN [CORP\GG-SQL-Admins] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CORP\GG-SQL-Admins];

Rola securityadmin zarządza loginami, resetuje hasła loginów SQL i nadaje uprawnienia na serwerze oraz w bazach, do których ma dostęp. Dokumentacja Microsoft każe jednak traktować ją jak równoważną sysadmin, bo możliwość nadawania dostępu i uprawnień pozwala przypisać większość uprawnień serwera. Tak samo należy traktować CONTROL SERVER. Od SQL Server 2022 (16.x) istnieją stałe role z prefiksem ##MS_ o wąskim zakresie: ##MS_LoginManager## tworzy i zmienia loginy, ale nie nadaje uprawnień, a ##MS_ServerStateReader## daje kontu monitoringu odczyt widoków DMV (VIEW SERVER STATE).

Role bazy i własne role na schemat

Stałe role bazy działają na całą bazę. db_datareader daje odczyt wszystkich tabel i widoków użytkownika, db_datawriter zmiany danych we wszystkich tabelach, ale żadna nie pozwala wykonywać procedur. db_ddladmin pozwala wykonywać dowolne polecenia DDL, a według dokumentacji jej członkowie mogą podnieść sobie uprawnienia. db_owner może w bazie wszystko, łącznie z jej usunięciem.

Dokładniejszy model to własne role z uprawnieniami na schemat. Obiekty aplikacji leżą w schemacie app, a rola dostaje prawo do całego schematu, więc nowe tabele i procedury są objęte uprawnieniami bez dodatkowych nadań. Gdy procedury i tabele mają tego samego właściciela, działa łańcuch własności: członek roli z samym EXECUTE uruchamia procedurę zmieniającą tabele, do których nie ma bezpośrednich praw.

USE CrmDB;

-- Odczyt tabel i widoków w schemacie app
CREATE ROLE crm_read;
GRANT SELECT ON SCHEMA::app TO crm_read;

-- Odczyt, zmiany danych i procedury w schemacie app
CREATE ROLE crm_write;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::app TO crm_write;

-- Członkami ról są użytkownicy grup z katalogu
ALTER ROLE crm_read ADD MEMBER [CORP\GG-SQL-CRM-Read];
ALTER ROLE crm_write ADD MEMBER [CORP\GG-SQL-CRM-Write];

Rola	Co daje	Komu nadawać
`sysadmin` (serwer)	wszystko na instancji, `DENY` nie działa	grupa `GG-SQL-Admins` i konta usług dodane przez instalator
`securityadmin` (serwer)	loginy, hasła, nadawanie uprawnień; równoważna `sysadmin`	nikomu spoza `sysadmin`; od SQL Server 2022 zamiast niej `##MS_LoginManager##`
`##MS_ServerStateReader##` (serwer, od 2022)	odczyt DMV (`VIEW SERVER STATE`)	konto monitoringu
`db_owner` (baza)	wszystko w bazie, także `DROP DATABASE`	administratorzy bazy, nie konto aplikacji
`db_ddladmin` (baza)	dowolne polecenia DDL	konto wdrażające zmiany schematu
`db_datareader` (baza)	`SELECT` na wszystkich tabelach i widokach użytkownika	raporty, które czytają całą bazę
`db_datawriter` (baza)	`INSERT`, `UPDATE`, `DELETE` na wszystkich tabelach użytkownika	rzadko; zwykle lepsza jest rola własna
własna rola z `GRANT … ON SCHEMA`	wybrane prawa w jednym schemacie, także `EXECUTE`	aplikacja i grupy użytkowników

Aplikacja z loginem SQL ma hasło w pliku konfiguracyjnym, zwykle jawnym tekstem, często także w repozytorium. Takie połączenie nie korzysta z Kerberosa, a zmiana hasła wymaga jednoczesnej zmiany na wszystkich serwerach aplikacji. Lepiej, gdy aplikacja działa na koncie Windows: pula aplikacji IIS albo usługa Windows na koncie gMSA, na przykład CORP\svc-crm-app$, albo na zwykłym koncie usługi. Ciąg połączenia nie zawiera wtedy hasła, a tożsamość potwierdza Windows. Tworzenie i instalację konta gMSA opisuje piąta część przewodnika.

{
  "ConnectionStrings": {
    "Crm": "Server=sql-crm.corp.example.com,14331;Database=CrmDB;Integrated Security=True"
  }
}

CREATE LOGIN [CORP\svc-crm-app$] FROM WINDOWS;

USE CrmDB;
CREATE USER [CORP\svc-crm-app$] FOR LOGIN [CORP\svc-crm-app$];
-- Aplikacja dostaje rolę własną, nie db_owner
ALTER ROLE crm_write ADD MEMBER [CORP\svc-crm-app$];

Tryb mieszany i konto sa

SQL Server działa w trybie uwierzytelniania Windows albo w trybie mieszanym, który dodatkowo przyjmuje loginy SQL. Gdy wszystkie aplikacje i osoby logują się kontami Windows, tryb mieszany nie jest potrzebny, a próba logowania loginem SQL zostawia w dzienniku wpis Server is configured for Windows authentication only. Tryb zmienia się w SSMS we właściwościach serwera, na stronie Security, i wymaga restartu usługi.

Konto sa istnieje zawsze i należy do roli sysadmin. Przy instalacji w trybie Windows jest wyłączone, po przełączeniu na tryb mieszany pozostaje wyłączone i tak powinno zostać. Zmiana nazwy jest dodatkiem: próby zgadywania hasła celują w nazwę sa, ale login zachowuje SID 0x01, więc da się go odnaleźć pod nową nazwą.

-- Logowanie jako sa przestaje działać; otwarte sesje i uprawnienia zostają
ALTER LOGIN sa DISABLE;
-- Nazwa, której nie da się zgadnąć
ALTER LOGIN sa WITH NAME = [sqlbuiltin_x7q2];

Polityka haseł dla loginów SQL

Gdy login SQL jest nieunikniony, o haśle decydują dwie opcje. CHECK_POLICY (domyślnie ON) stosuje politykę haseł Windows obowiązującą na serwerze SQL, w domenie zwykle pochodzącą z polityki domeny: długość, złożoność i blokadę po nieudanych próbach. CHECK_EXPIRATION (domyślnie OFF) dodaje wygasanie hasła. Kombinacja CHECK_POLICY = OFF i CHECK_EXPIRATION = ON nie jest obsługiwana.

Dla loginu aplikacji rozsądny zestaw to CHECK_POLICY = ON i CHECK_EXPIRATION = OFF z planową zmianą hasła, bo wygasanie oznacza awarię aplikacji w dniu utraty ważności. Login SQL używany przez osobę powinien mieć obie opcje włączone i pierwsze hasło z MUST_CHANGE.

-- Złożoność i blokada z polityki Windows, bez wygasania hasła
CREATE LOGIN crm_app WITH PASSWORD = '<silne-haslo>', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

SQL Server 2025 (17.x) zapisuje skróty haseł loginów SQL algorytmem PBKDF2 (RFC 2898, 100 000 iteracji SHA-512), co znacznie spowalnia odgadywanie haseł metodą siłową, a samo logowanie nieco wydłuża, głównie w aplikacjach bez puli połączeń.

Użytkownik bez loginu po odtworzeniu bazy

W sytuacji z początku wpisu odtworzona baza zawiera użytkownika crm_app z SID-em loginu z srv-sql-01, a login na srv-sql-02 ma nowy SID. To użytkownik osierocony (orphaned user). ALTER USER … WITH LOGIN przepisuje w bazie SID użytkownika na SID wskazanego loginu, a role i uprawnienia zostają bez zmian. Przestarzała procedura sp_change_users_login nie obsługuje loginów Windows, a dokumentacja zastępuje ją właśnie ALTER USER.

USE CrmDB;
-- Użytkownik dostaje SID loginu crm_app z tej instancji
ALTER USER crm_app WITH LOGIN = crm_app;

Gdy kopia trafia na drugi serwer regularnie, na przykład na serwer zapasowy z szóstej części przewodnika, login SQL zakłada się tam z tym samym SID-em co na pierwszym serwerze. Microsoft udostępnia też skrypt sp_help_revlogin, który generuje polecenia CREATE LOGIN z oryginalnym SID-em i skrótem hasła dla wszystkich loginów instancji.

-- Na srv-sql-01: odczyt SID loginu
SELECT name, sid FROM sys.sql_logins WHERE name = N'crm_app';

-- Na srv-sql-02: ten sam SID, więc kolejne kopie bazy pasują bez naprawy
CREATE LOGIN crm_app WITH PASSWORD = '<silne-haslo>', SID = 0x8E2B4C6A1D3F4E7B9A0C5D2E6F1A3B4C,
    CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;

Loginy Windows tego problemu nie mają: SID pochodzi z katalogu, więc po utworzeniu loginu [CORP\GG-SQL-CRM-Read] na nowym serwerze użytkownik w odtworzonej bazie pasuje od razu.

Domain Admins bez roli sysadmin w SSMS

Login grupy Domain Admins ma rolę sysadmin, ale administrator zalogowany na serwerze SQL otwiera SSMS i dostaje błąd logowania albo nie widzi baz. Przyczyną jest kontrola konta użytkownika (UAC). Proces uruchomiony bez podniesienia uprawnień dostaje token filtrowany, w którym grupy uprzywilejowane, takie jak Administrators i Domain Admins, służą wyłącznie do odmowy dostępu. SQL Server nie dopasowuje do nich loginu grupy i zapisuje w dzienniku błąd 18456, zwykle ze stanem 11.

Z innej maszyny ten sam użytkownik ma rolę sysadmin, bo logowanie sieciowe kontem domenowym nie jest filtrowane. Lokalnie pomaga uruchomienie SSMS jako administrator, ale lepszym rozwiązaniem jest dedykowana grupa GG-SQL-Admins z rolą sysadmin zamiast loginu Domain Admins. Konta z grupy Domain Admins nie powinny w ogóle logować się na serwery członkowskie, co opisuje model warstw kont uprzywilejowanych.

Sprawdź u siebie

Członkowie ról sysadmin i securityadmin. Dobry wynik w sysadmin to wyłączone sa (także pod zmienioną nazwą), identyfikatory usług NT SERVICE\… i grupa GG-SQL-Admins, a w securityadmin brak wierszy. Konto osobowe, BUILTIN\Administrators, Domain Admins albo login aplikacji w którejkolwiek z ról to wynik do poprawy.

SELECT r.name AS role_name, m.name AS member_name, m.type_desc, m.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
WHERE r.name IN (N'sysadmin', N'securityadmin')
ORDER BY r.name, m.name;

Tryb uwierzytelniania i polityka haseł loginów SQL. windows_auth_only równe 1 oznacza tryb Windows. Każdy włączony login powinien mieć is_policy_checked = 1, a sa wartość is_disabled = 1. Włączony login z is_policy_checked = 0 lub is_locked = 1 albo login, którego nikt nie przypisze do aplikacji, wymaga wyjaśnienia.

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

-- Loginy wewnętrzne z prefiksem ## pominięte
SELECT name, is_disabled, is_policy_checked, is_expiration_checked,
       LOGINPROPERTY(name, 'IsLocked') AS is_locked,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set
FROM sys.sql_logins
WHERE name NOT LIKE N'##%'
ORDER BY is_disabled, is_policy_checked, name;

Użytkownicy bez pasującego loginu, w każdej bazie użytkownika, z pominięciem systemowych i utworzonych celowo bez loginu (authentication_type_desc = 'NONE'). Pusty wynik jest dobry. SQL_USER to użytkownik osierocony do naprawy przez ALTER USER … WITH LOGIN. WINDOWS_USER albo WINDOWS_GROUP to konto bez własnego loginu: może wchodzić przez login grupy, ale częściej to pozostałość po usuniętym koncie albo grupa bez loginu na nowym serwerze.

USE CrmDB;
SELECT dp.name AS user_name, dp.type_desc, dp.authentication_type_desc, dp.sid
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON sp.sid = dp.sid
WHERE sp.sid IS NULL
  AND dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc IN (N'INSTANCE', N'WINDOWS')
  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')
ORDER BY dp.type_desc, dp.name;

Członkowie grupy administratorów widziani przez SQL Server. Pierwsze wywołanie powinno zwrócić wiersz z privilege równym admin; pusty wynik oznacza, że grupa nie ma loginu. Drugie zwraca bezpośrednich członków grupy (bez grup zagnieżdżonych) i lista powinna odpowiadać osobom, które mają administrować instancjami. Błąd oznacza, że SQL Server nie rozwiązuje nazwy grupy albo nie łączy się z kontrolerem domeny; procedura obsługuje tylko grupy globalne.

-- privilege = admin: grupa ma rolę sysadmin
EXEC xp_logininfo 'CORP\GG-SQL-Admins';
-- Bezpośredni członkowie grupy według kontrolera domeny
EXEC xp_logininfo 'CORP\GG-SQL-Admins', 'members';