SQL-Server 2022 Permissions

There are two main approaches to create, modify, or delete a database in SQL Server 2022, each requiring different permissions:

1. Using Server-Level Permissions:
   • To create a database: You need either:
     • sysadmin fixed server role membership: This grants unlimited permissions on the server, including creating databases.
     • dbcreator fixed server role membership: This specifically allows creating databases.
   • To modify a database: You typically need:
     • CONTROL permission on the database: This grants various management permissions, including modifying the database schema and data.
     • ALTER ANY DATABASE permission: This allows modifying any database on the server, including schema and data
   • To delete a database: You typically need:
     • CONTROL permission on the database: This grants the ability to delete the database.
     • DROP ANY DATABASE permission: This allows deleting any database on the server.
2. Using Database-Level Permissions:
   • To create a database: This is not possible directly using database-level permissions.
   • To modify a database: You can achieve some modifications with database-level roles:
     • db_owner database role membership: Grants almost complete control over the database schema and data.
     • db_datawriter database role membership: Allows modifying data within the database.
     • db_ddladmin database role membership: Allows making schema changes within the database.
   • To delete a database: This is not possible directly using database-level permissions. You need server-level permissions like CONTROL or DROP ANY DATABASE as mentioned earlier.

Important Notes:

• These are general guidelines, and specific permissions might vary depending on the database security configuration.
• Granting excessive permissions can be a security risk, so it's crucial to assign the minimum permissions necessary for each user or role.
• Consulting the official documentation and seeking expert advice is recommended for a secure and efficient database setup.

The person who creates a database is referred to as the database owner, or more commonly known as the DBO. The person who installs SQL Server 2022 is generally referred to as the system administrator, or SA. To create, modify, or delete databases, you must be assigned privileges, or permissions, to do so. The following table illustrates the permissions required to perform each of these actions:

Action	Required Permission
Create	SA or those who have CREATE DATABASE permissions
Modify	DBO (with CREATE DATABASE permissions) or SA
Delete	DBO (with CREATE DATABASE permissions) or SA

In the next lesson, we will take an initial look at actually creating a database.

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