Database Permissions in SQL Server 2025

Creating a database, as the previous two lessons showed, isn't something every login can do. Whether you're sitting at SSMS, running a sqlcmd script, or publishing a DACPAC through SqlPackage, the login behind that connection still has to clear the same permission check SQL Server applies no matter which of the tools from the last lesson happened to issue the statement. This lesson lists exactly which permissions and role memberships are required to create, modify, or delete a database, introduces the database owner, the role every database has exactly one of, and shows how to check which of these permissions a login already has before you even attempt the statement.

SQL Server actually offers two different server-level permissions that cover database creation, one considerably older than the other. The original CREATE DATABASE permission is a quirky special case: it can only be granted while connected to the master database, nowhere else, a holdover from versions of SQL Server that predate the more general permission system in use today. Since SQL Server 2014, a second, more conventional permission, CREATE ANY DATABASE, does the same job without that restriction, and is the one Microsoft now recommends granting.

USE master;
GRANT CREATE DATABASE TO TrainingUser;

GRANT CREATE ANY DATABASE TO TrainingUser;

A login doesn't need either permission individually if it already has one of these instead:

• Membership in the dbcreator fixed server role
• Membership in the sysadmin fixed server role, which includes every permission there is
• The CONTROL SERVER permission, which is functionally equivalent to sysadmin membership

It's worth being precise about why two permissions exist for the same job. The original CREATE DATABASE permission dates back to a much older, simpler SQL Server permission model, one built around a short list of statement-level permissions that could each only be granted from a specific database, and master happened to be the one that governed database creation. By the time server-level, securable-based permissions like CREATE ANY DATABASE arrived in SQL Server 2014, the older permission had enough scripts and documentation depending on it that Microsoft kept it working rather than removing it. In new work there's little reason to reach for the older form; it's covered here mainly so that a GRANT CREATE DATABASE statement you encounter in legacy setup scripts doesn't look like a typo for CREATE ANY DATABASE.

Modifying an existing database, through ALTER DATABASE, generally requires either the ALTER permission on that specific database, or membership in its db_owner fixed database role:

GRANT ALTER ON DATABASE::TrainingDB TO TrainingUser;

A few specific modifications narrow that further. Shrinking a database needs the ALTER permission. Changing a database-level option, such as its recovery model, needs ALTER or CONTROL:

ALTER DATABASE TrainingDB SET RECOVERY SIMPLE;

Adding a data or log file to an existing database, or changing one of its files' size or growth settings, falls under that same ALTER permission:

ALTER DATABASE TrainingDB
MODIFY FILE (NAME = TrainingDB_log, SIZE = 256MB);

Renaming a database is the one modification that needs more than just ALTER. It also requires membership in dbcreator or sysadmin, since a rename touches the instance-wide database list recorded in master, not just the database being renamed:

ALTER DATABASE TrainingDB MODIFY NAME = TrainingDB_Archive;

Unlike CREATE DATABASE, there's no permission literally named DROP DATABASE. Dropping a database requires one of:

• The ALTER ANY DATABASE permission at the server level
• The CONTROL permission on the database itself
• Membership in the dbcreator fixed server role
• Membership in the sysadmin fixed server role

GRANT ALTER ANY DATABASE TO TrainingUser;

One practical wrinkle applies regardless of which permission or role membership grants the right to drop a database: SQL Server won't drop a database that's currently in use by another session. The connection issuing DROP DATABASE has to be the only one with that database open, or the statement fails with an error rather than forcibly disconnecting anyone else. Dropping a database also deletes its underlying data and log files from disk; there's no permission that lets you drop the catalog entry while leaving the files behind.

SQL Server ships with a handful of fixed server roles, each bundling together permissions for one area of administration. Only a few bear directly on creating, modifying, or dropping a database:

Role	Capability
sysadmin	Can perform every activity in SQL Server
dbcreator	Can create, alter, drop, and restore any database
serveradmin	Can modify server-wide configuration options and shut down the server

The remaining fixed server roles, securityadmin, setupadmin, diskadmin, bulkadmin, and processadmin, each cover a different slice of server administration, logins, linked servers, disk files, bulk-import operations, and running processes, respectively, but none of them grant the ability to create, alter, or drop a database on their own.

Within a single database, membership in the db_owner fixed database role grants every database-level maintenance and configuration right, including the modify and, within that one database, drop permissions covered above:

USE TrainingDB;
ALTER ROLE db_owner ADD MEMBER TrainingUser;

Unlike dbcreator or sysadmin, which operate at the server level and apply across every database on the instance, db_owner is scoped to the one database it's granted in; a login can be a db_owner of TrainingDB without having any special rights at all in a different database on the same server.

Two other fixed database roles are worth knowing by name, even though neither one alone covers the create, modify, or drop permissions this lesson is about. db_ddladmin can run the data-definition statements that create, alter, and drop tables, views, and other objects inside the database, but can't grant permissions to anyone else. db_securityadmin can manage role membership and permissions within the database, but can't run DDL itself. Between them they split apart the two halves of what db_owner does in one combined role, which is occasionally useful when you want someone able to manage object structure without also being able to manage who else has access, or the reverse.

Every database has exactly one owner, commonly shortened to the DBO, and it's whoever is recorded in the database's own metadata as such, by default the login that created it, though ownership can be reassigned afterward with ALTER AUTHORIZATION:

ALTER AUTHORIZATION ON DATABASE::TrainingDB TO NewOwnerLogin;

Practically, being the DBO is equivalent to db_owner membership: both grant full control over that one database's structure, security, and maintenance, without necessarily granting anything at the server level or in any other database. A login with sysadmin membership is automatically treated as the DBO of every database on the instance, which is part of why sysadmin membership is powerful enough to require careful, deliberate granting. The current owner of any database is visible directly from the catalog:

SELECT name AS DatabaseName, SUSER_SNAME(owner_sid) AS OwnerLogin
FROM sys.databases
ORDER BY name;

A common scenario shows why this distinction matters in practice. Suppose a contractor's login created TrainingDB early in the course project and has since left the organization; that login might get disabled or dropped entirely. Until ownership is explicitly reassigned with ALTER AUTHORIZATION, the database's owner reference can end up pointing at a login that no longer exists, which complicates anything that depends on resolving the owner, including some restore and attach operations. Reassigning ownership to a current, active login as part of offboarding avoids that problem entirely.

Older material sometimes uses "SA" loosely to mean "whoever installed SQL Server" or "the administrator." More precisely, sa is the name of one specific, built-in SQL login created automatically during setup, a SQL Server authentication account with full sysadmin rights by default. Plenty of installations disable the sa login entirely in favor of named logins that are individually granted sysadmin membership, which is generally the more secure, auditable approach, since actions taken under a shared sa login can't be traced back to the specific person who took them. What actually matters for the permissions covered in this lesson is sysadmin membership itself, not whether the particular login happens to be named sa. The sa login only exists at all when SQL Server authentication is enabled; an instance configured for Windows Authentication mode exclusively, which is itself the more secure of the two authentication modes, has no working sa login to disable in the first place.

Before assuming a CREATE DATABASE statement will fail or succeed, it's often faster to just ask SQL Server directly what the current login is allowed to do. IS_SRVROLEMEMBER() checks membership in a server role:

SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysadmin,
       IS_SRVROLEMEMBER('dbcreator') AS IsDbcreator;

A result of 1 means the current login is a member, 0 means it isn't, and NULL means the role name itself wasn't recognized. For a specific permission rather than a role, HAS_PERMS_BY_NAME() answers the same kind of question at a finer grain:

SELECT HAS_PERMS_BY_NAME(NULL, 'SERVER', 'CREATE ANY DATABASE') AS CanCreateDatabase;

Both functions are considerably faster than simply trying the statement and seeing whether it errors, especially when you're troubleshooting a permissions problem for someone else's login rather than your own. For a broader view that doesn't require naming each permission one at a time, sys.fn_my_permissions lists every permission the current login actually holds on a given securable:

SELECT *
FROM sys.fn_my_permissions(NULL, 'SERVER')
WHERE permission_name LIKE '%DATABASE%';

And when the question is which logins already hold a given role, rather than what the current login can do, the role-membership catalog views answer it directly:

SELECT sp.name AS LoginName, r.name AS RoleName
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS sp ON sp.principal_id = srm.member_principal_id
JOIN sys.server_principals AS r ON r.principal_id = srm.role_principal_id
WHERE r.name IN ('sysadmin', 'dbcreator');

The equivalent view at the database level, sys.database_role_members, answers the same question for db_owner and the other fixed database roles within whichever database you're connected to.

Azure SQL Database's permission model differs in one notable way: since a single database or elastic pool doesn't expose the same server-level fixed roles as an on-premises instance, the equivalent of dbcreator is the dbmanager role, granted in the logical server's master database, which allows creating and dropping databases on that logical server. A companion role, loginmanager, handles creating and managing logins at that same logical-server level, a job securityadmin handles on-premises.

That distinction is specific to Azure SQL Database. Azure SQL Managed Instance, a different deployment option within the Azure SQL family, runs much closer to an on-premises instance and does support the familiar fixed server roles, including sysadmin and dbcreator, for a login granted through its server-level admin. Which model applies depends on which of the two you're working with, not on the fact that it's "Azure SQL" generically.

As with any permission system, grant the narrowest role or permission that actually covers what a login needs to do. A login that only needs to create training databases for this course doesn't need full sysadmin membership just because that's the easiest box to check; granting CREATE ANY DATABASE specifically, as shown earlier, accomplishes the same goal without also handing over the ability to drop every other database on the instance, read every other database's data, or change server-wide configuration.

The same reasoning applies to the service account behind an automated deployment pipeline, the kind of setup where SqlPackage or Invoke-Sqlcmd from the last lesson runs unattended on a schedule. That account typically needs to create and tear down databases as part of each deployment, but it has no legitimate reason to touch logins, server configuration, or any database outside the ones the pipeline manages. CREATE ANY DATABASE, paired with ALTER ANY DATABASE for the teardown step, covers that job without the account ever needing sysadmin at all, which matters because a compromised pipeline credential with narrow permissions does far less damage than one holding full administrative rights over the instance.

With several permissions, roles, and special cases covered above, a single reference table makes the overall pattern easier to hold onto:

Action	Specific Permission	Role Membership That Also Covers It
Create	CREATE DATABASE (in master) or CREATE ANY DATABASE	dbcreator, sysadmin
Modify	ALTER on the database	db_owner (that database only), sysadmin
Delete	ALTER ANY DATABASE or CONTROL on the database	dbcreator, sysadmin

In the next lesson, you'll take an initial, hands-on look at actually creating a database, building directly on CREATE DATABASE's syntax, this time with the permissions covered here already in place.

Before moving on to the next lesson, click the Quiz button to check your knowledge of the material covered so far on databases with a short, multiple-choice quiz.
Database Permissions - Quiz