DB-유

Understanding SQL Server Server Roles and Database Roles

· mssql, security, permissions

Every time a developer pings me with "it says access denied but I'm in the role", it turns out they're in the wrong role at the wrong level. SQL Server has two layers of principals: logins at the instance level and users at the database level. Roles exist at both, and being in a server role says nothing about what the mapped user can do inside a particular database (sysadmin is the one big exception).

Below are the fixed roles at both levels, how I check membership, and how to stop handing db_owner to every application account.

Fixed server roles

Server roles are instance-wide. The fixed ones cannot be changed, and their permissions are:

  • sysadmin – can do anything on the instance. Bypasses all permission checks, including database-level ones.
  • serveradmin – change server-wide configuration (sp_configure) and shut down the server.
  • securityadmin – manage logins and their properties, GRANT/DENY/REVOKE server-level permissions. Effectively equivalent to sysadmin, because it can grant itself more rights.
  • processadmin – end sessions with KILL.
  • setupadmin – add and remove linked servers.
  • bulkadmin – run BULK INSERT.
  • diskadmin – manage backup devices.
  • dbcreator – create, alter, drop and restore any database.
  • public – every login is a member; only grant to it what truly everyone should have.

To list who is in which server role:

SELECT r.name AS server_role, m.name AS member, m.type_desc
FROM sys.server_role_members rm
JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals m ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

Adding and removing members uses ALTER SERVER ROLE, which replaced sp_addsrvrolemember in SQL Server 2012:

ALTER SERVER ROLE dbcreator ADD MEMBER [CONTOSO\DeployService];
ALTER SERVER ROLE sysadmin DROP MEMBER [CONTOSO\OldAdmin];

Fixed database roles

Every database has its own set of fixed roles. Membership is per database, so a login can be db_owner in one database and have no user at all in another.

  • db_owner – full control of the database, including dropping it.
  • db_securityadmin – manage role membership and permissions.
  • db_accessadmin – add and remove database users.
  • db_backupoperator – run BACKUP DATABASE and BACKUP LOG.
  • db_ddladmin – create, alter and drop objects, but not read or write data.
  • db_datareader – SELECT on every user table and view.
  • db_datawriter – INSERT, UPDATE, DELETE on every user table.
  • db_denydatareader / db_denydatawriter – explicit DENY, which wins over any GRANT.

Check membership inside a database:

USE Sales;
SELECT r.name AS db_role, m.name AS member
FROM sys.database_role_members rm
JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals m ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

And change it with ALTER ROLE, again the replacement for the older sp_addrolemember:

ALTER ROLE db_datareader ADD MEMBER ReportUser;
ALTER ROLE db_owner DROP MEMBER AppUser;

A custom role instead of db_owner

Applications usually need to read and write tables and run stored procedures. None of the fixed roles grants EXECUTE, which is exactly why people end up handing out db_owner. I don't like that, so I make a role:

USE Sales;
CREATE ROLE app_executor;
GRANT EXECUTE ON SCHEMA::dbo TO app_executor;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_executor;
ALTER ROLE app_executor ADD MEMBER AppUser;

Granting at the schema level means new procedures and tables are covered automatically, so you do not have to touch permissions with every deployment.

User-defined server roles

Since SQL Server 2012 you can define server roles too. My usual case is a monitoring account that must read DMVs everywhere without being sysadmin:

CREATE SERVER ROLE monitoring;
GRANT VIEW SERVER STATE TO monitoring;
GRANT VIEW ANY DEFINITION TO monitoring;
ALTER SERVER ROLE monitoring ADD MEMBER [CONTOSO\svc_monitor];

Checking what an account can really do

Don't trust the role list alone. I impersonate the account and ask:

EXECUTE AS LOGIN = 'CONTOSO\svc_monitor';
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
REVERT;

USE Sales;
EXECUTE AS USER = 'AppUser';
SELECT * FROM fn_my_permissions('dbo.usp_GetOrders', 'OBJECT');
REVERT;

IS_SRVROLEMEMBER('sysadmin') and IS_ROLEMEMBER('db_owner') are handy in scripts that need a yes/no answer.

Gotchas

  • "The server principal 'X' is not able to access the database 'Y' under the current security context." The login exists but has no user in that database. Create one with CREATE USER X FOR LOGIN X before adding it to a role.
  • Members of db_owner can still be blocked by a DENY at the server level, but nothing blocks sysadmin. Treat sysadmin membership as domain admin equivalent and review it regularly.
  • securityadmin looks harmless in the list but it can grant CONTROL SERVER or add itself to sysadmin. Audit it as strictly as sysadmin.
  • After a restore onto another instance, database users become orphaned from their logins. ALTER USER AppUser WITH LOGIN = AppUser re-links them; role membership inside the database is preserved.

My rule: logins get the minimum server role (often none), each database gets a user-defined role with schema-level grants, and db_owner and sysadmin stay with the DBA team. Onboarding takes a few minutes longer. Worth it.