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 role | Description |
|---|---|
| sysadmin | Members of the sysadmin fixed server role can perform any activity in the server. Important: permissions can't be denied to members of this role. |
| serveradmin | Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server. |
| securityadmin | Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. securityadmin can also GRANT, DENY, 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##. |
| processadmin | Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server. |
| setupadmin | Members 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.) |
| bulkadmin | Members 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. |
| diskadmin | The diskadmin fixed server role is used for managing disk files. |
| dbcreator | Members of the dbcreator fixed server role can create, alter, drop, and restore any database. |
| public | Every 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 name | Description |
|---|---|
| db_owner | Members 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_securityadmin | Members 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_accessadmin | Members 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_backupoperator | Members of the db_backupoperator fixed database role can back up the database. |
| db_ddladmin | Members 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_datawriter | Members 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_datareader | Members 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_denydatawriter | Members of the db_denydatawriter fixed database role can't add, modify, or delete any data in the user tables within a database. |
| db_denydatareader | Members of the db_denydatareader fixed database role can't read any data from the user tables and views within a database. |

Comments
Post a Comment