Tell me Server level permission and DB level permission in SQL Server?

There are two type of  security role SERVER LEVEL and DATABASE LEVEL.

Server level Fixed Roles:

Fixed server-level roleDescription
sysadminMembers of the sysadmin fixed server role can perform any activity in the server. Important: permissions can't be denied to members of this role.
serveradminMembers of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadminMembers of the securityadmin fixed server role manage logins and their properties. They can GRANTDENY, and REVOKE server-level permissions. securityadmin can also GRANTDENY, and REVOKE database-level permissions if they have access to a database. Additionally, securityadmin can reset passwords for SQL Server logins.

IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role. As an alternative, starting with SQL Server 2022 (16.x), consider using the new fixed server role ##MS_LoginManager##.
processadminMembers of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
setupadminMembers of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)
bulkadminMembers of the bulkadmin fixed server role can run the BULK INSERT statement.

The bulkadmin role or ADMINISTER BULK OPERATIONS permissions isn't supported for SQL Server on Linux. Only the sysadmin can perform bulk inserts for SQL Server on Linux.
diskadminThe diskadmin fixed server role is used for managing disk files.
dbcreatorMembers of the dbcreator fixed server role can create, alter, drop, and restore any database.
publicEvery SQL Server login belongs to the public server role. When a server principal isn't granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users. You can't change membership in public.

Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

Database level Fixed Roles: 

Fixed database role nameDescription
db_ownerMembers of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also DROP the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and can't be performed by db_owners.)
db_securityadminMembers of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
db_accessadminMembers of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperatorMembers of the db_backupoperator fixed database role can back up the database.
db_ddladminMembers of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. Members of this role can potentially elevate their privileges by manipulating code that might get executed under high privileges and their actions should be monitored.
db_datawriterMembers of the db_datawriter fixed database role can add, delete, or change data in all user tables. In most use cases, this role is combined with db_datareader membership to allow reading the data that is to be modified.
db_datareaderMembers of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.
db_denydatawriterMembers of the db_denydatawriter fixed database role can't add, modify, or delete any data in the user tables within a database.
db_denydatareaderMembers of the db_denydatareader fixed database role can't read any data from the user tables and views within a database.


Comments

Popular posts from this blog

SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling)

How to use Azure Automatic performance tuning ?

How to Upgrade SQL Server from 2014 to 2022, Apply the Latest Patches Post-Upgrade, and Install the Latest SSMS Tool?