Gestionarea rolurilor utilizatorilor cu Microsoft SQL Server Management Studio

Utilizați Microsoft SQL Server Management Studio (SSMS) pentru a vizualiza o listă a rolurilor fixe din SQL Server. Porniți SSMS și conectați-vă la database engine.

Rolurile fixe ale serverului sunt disponibile în secțiunea Security > Server roles. Următoarele roluri fixe ale serverului sunt disponibile în SQL Server 2019 și versiunile anterioare:

• sysadmin
• serveradmin
• securityadmin
• processadmin
• setupadmin
• bulkadmin
• diskadmin
• dbcreator
• public

SQL Server 2022 și versiunile ulterioare

Începând cu SQL Server 2022, Microsoft a introdus roluri de server fixe suplimentare, bazate pe principiul privilegiului minim. Aceste roluri oferă permisiuni de administrare mai detaliate și este de recomandat să le utilizați în locul rolurilor tradiționale, cu drepturi mai largi, ori de câte ori este posibil.

Printre cele mai utilizate roluri fixe de server din SQL Server 2022 se numără:

– ##MS_DatabaseConnector: Permite membrilor să se conecteze la orice bază de date fără a fi necesar un cont de utilizator specific în cadrul acelei baze de date.
– ##MS_LoginManager: Permite crearea, modificarea și gestionarea datelor de autentificare SQL Server fără a acorda privilegii complete de administrator de securitate.
– ##MS_DatabaseManager: Permite crearea, modificarea și ștergerea bazelor de date. Persoana care creează baza de date devine proprietarul acesteia (dbo).
– ##MS_ServerStateManager: Acordă permisiuni pentru vizualizarea și gestionarea setărilor și operațiunilor legate de starea serverului.
– ##MS_ServerStateReader: Permite vizualizarea informațiilor privind starea serverului, inclusiv a vizualizărilor de gestionare dinamică (DMV) și a datelor de monitorizare.
– ##MS_ServerPerformanceStateReader: Permite în mod specific vizualizarea informațiilor privind starea serverului legate de performanță și a datelor de diagnosticare.
– ##MS_ServerSecurityStateReader: Permite vizualizarea stării serverului legate de securitate și a metadatelor de securitate.
– ##MS_DefinitionReader: Acordă acces pentru a vizualiza definițiile metadatelor pentru obiectele serverului și ale bazelor de date.
– ##MS_PerformanceDefinitionReader: Permite vizualizarea definițiilor obiectelor și a metadatelor legate de performanță.
– ##MS_SecurityDefinitionReader: Permite vizualizarea definițiilor obiectelor, a permisiunilor și a metadatelor legate de securitate.

Nota bene: Rolurile clasice fixe ale serverului rămân disponibile din motive de compatibilitate cu versiunile anterioare. Cu toate acestea, SQL Server 2022 introduce rolurile ##MS_*## pentru a susține principiul privilegiului minim, acordând permisiuni de administrare cu un domeniu de aplicare mai restrâns.

Roluri de bază de date

Fiecare bază de date are propriul set de roluri fixe la nivel de bază de date. Extindeți secțiunea Database > Security > Roles > Database Roles:

• db_owner
• db_securityadmin
• db_accessadmin
• db_backupoperator
• db_ddladmin
• db_datawriter
• db_datareader
• db_denydatawriter
• db_denydatareader
• public

Cum se adaugă sau se afișează rolurile utilizatorilor în SQL Server cu ajutorul interogărilor Transact-SQL

Puteți utiliza interogări în limbajul T-SQL pentru a adăuga sau a elimina roluri SQL Server pentru utilizatori.

Deschideți SSMS și faceți clic pe „New Query” (Interogare nouă).

Puteți utiliza următoarea interogare pentru a afișa toate rolurile de server fixe și definite de utilizator disponibile pe instanța SQL Server:

SELECT name
FROM sys.server_principals
WHERE type = ‘R’
ORDER BY name;

Puteți utiliza interogarea de mai jos pentru a afișa toate rolurile din baza de date selectată în prezent:

SELECT name
FROM sys.database_principals
WHERE type = ‘R’
ORDER BY name;

Pentru a adăuga un utilizator AD la conturile de autentificare ale serverului SQL, utilizați următoarea interogare:

CREATE LOGIN [class\ion.popescu]
FROM WINDOWS;
GO

Acum puteți atribui un rol de server unui utilizator:

ALTER SERVER ROLE serveradmin
ADD MEMBER [class\ion.popescu];

Atribuiți un rol la nivel de bază de date:

USE DBNAME;
GO

CREATE USER [class\ion.popescu]
FOR LOGIN [class\ion.popescu];

ALTER ROLE db_backupoperator
ADD MEMBER [class\ion.popescu];

Pentru a elimina un utilizator din rolul de server, executați:

ALTER SERVER ROLE serveradmin
DROP MEMBER [class\ion.popescu];

Sau dintr-un rol de bază de date:

ALTER ROLE db_backupoperator
DROP MEMBER [class\ion.popescu];

Enumerați rolurile de pe serverul de liste la care un utilizator este membru:

SELECT r.name as Role, m.name as Principal

from

master.sys.server_role_members rm

inner join

master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = ‘R’

inner join

master.sys.server_principals m on m.principal_id = rm.member_principal_id

WHERE m.name = ‘class\ion.popescu’

Cum se verifică apartenența/membership la un rol curent

Pentru a verifica rapid dacă utilizatorul conectat în prezent aparține unui anumit rol de server/bază de date, puteți folosi funcțiile integrate de verificare a apartenenței la roluri.

Pentru a verifica apartenența la un rol de server, utilizați următoarea interogare:

SELECT IS_SRVROLEMEMBER(‘sysadmin’);

Pentru a verifica dacă un utilizator face parte dintr-un rol din baza de date:

SELECT IS_ROLEMEMBER(‘db_owner’);

O valoare de returnare egală cu 1 indică faptul că utilizatorul curent este membru al rolului, 0 indică faptul că nu este, iar NULL înseamnă că rolul sau entitatea nu au putut fi validate.

Interogare privind rolurile atribuite utilizatorilor într-o anumită bază de date:

use DBNAME;
select P.Name, R.Name
from sys.database_principals P
left outer join sys.database_role_members RM on P.principal_id=RM.member_principal_id
left outer join sys.database_principals R on R.principal_id=RM.role_principal_id
where P.Name = ‘class\ion.popescu’
ORDER BY R.Name;