REQUEST COMPLIMENTARY SQLS*PLUS LICENCE
Assigning Permissions and Roles in SQL Server
SQL Server applies role-based security rights delimitation. A role is a certain set of rights that can be assigned to a certain user or group of users.
In SQL Server there are default server and database level roles, which have a predefined set of permissions assigned to them.
You can also create your own roles for which the administrator can define a list of permissions and bans. Default Server and Database Level Roles have a predefined set of permissions, each at its own level and they cannot be changed.
The article below describes the default Server and Database Level Roles that can be assigned to users and groups and how to assign them.
Built-in SQL Server server level roles
The table below shows the default server level roles and a brief description of them:
| Role Title | Role description |
| sysadmin | This server role allows you to perform any actions on the server. |
| serveradmin | The serveradmin server roles allow you to change configuration settings at the server level, as well as to turn it off. |
| securityadmin | Those who are assigned this role can manage logins, their rights and their properties. They can also grant, deny and revoke database level permissions if they have access to the database. In addition, they can reset passwords for SQL Server login names. The securityadmin role can be considered equivalent to the sysadmin role, allowing any permissions to be granted. |
| processadmin | This server role allows you to terminate processes running on an SQL Server instance. |
| setupadmin | The setupadmin server role can add or remove related servers using Transact-SQL instructions . |
| bulkadmin | Owners of this role can follow the BULK INSERT instructions. |
| diskadmin | The diskadmin server role is used to manage files on disk. |
| dbcreator | This role allows you to create, modify, delete and restore any database. |
| public | The public role is contained in each database, including system databases. It cannot be deleted, nor can users be added or removed from it. The permissions granted to the public roles are inherited by all other users and roles because they belong to the default public role. You should only grant public roles permissions that are required by all users. |
The roles listed in the table are based on the 34 standard server level permissions that are allowed or denied for the built-in role, below is the list of server level permissions:
BULK OPERATIONS
ANY AVAILABILITY GROUP
ANY CONNECTION
ANY CREDENTIAL
ANY DATABASE
ANY ENDPOINT
ANY EVENT NOTIFICATION
ANY EVENT SESSION
ANY CONNECTED SERVER
ANY LOGIN
ANY SERVER AUDIT
ANY SERVER ROLE
ADDITIONAL RESOURCES
SERVER STATE
ALTER SETTINGS
ALTER TRACE
AUTHENTICATE SERVER
CONNECT ANY DATABASE
CONNECT SQL
CONTROL SERVER
CREATE ANY DATABASE
CREATE AVAILABILITY GROUP
CREATE DDL EVENT NOTIFICATION
CREATE ENDPOINT
CREATE SERVER ROLE
CREATE TRACE EVENT NOTIFICATION
EXTERNAL ACCESS ASSEMBLY
IMPERSONATE ANY LOGIN
SELECT ALL USER SECURABLES
SHUTDOWN
UNSAFE ASSEMBLY
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
Built-in SQL Server Database Level Roles
The table below describes the database level roles that exist in all databases and summarizes the rights that these roles provide.
All the database level roles listed below are built-in, and their rights cannot be changed.
| Role Title | Role description |
| db_owner | This role gives the right to perform all the configuration and maintenance operations on the database, as well as to delete the database. |
| db_securityadmin | Roles db_securityadmin allows you to manage user roles and permissions, including their own. |
| db_accessadmin | This role allows adding or deleting remote access rights to the database. |
| db_backupoperator | The role db_backupoperator allows you to create database backups. |
| db_ddladmin | This role allows you to execute any Data Definition Language (DDL) commands in the database. |
| db_datawriter | This role allows you to add, delete or modify data in all tables. |
| db_datareader | The role db_datareader allows you to read all data from all tables. |
| db_denydatawriter | Those to whom this role is assigned cannot add, change or delete data in database tables. |
| db_denydatareader | Owners of this role cannot read data from database tables. |
The role of public was described in the table above, it is on every damage, including the database level. All default created roles, whether database level or server roles have invariable permissions, you can not add new permissions or remove them, the only exception is the public role.
At the database level, there is also a list of 77 default permissions, some of which are already included in the server level roles and permissions:
ALTER
ANY APPLICATION ROLE
ANY ASSEMBLY
ANY ASYMMETRIC KEY
ANY CERTIFICATE
ANY COLUMN ENCRYPTION KEY
ANY COLUMN MASTER KEY
ANY CONTRACT
ANY DATABASE AUDIT
ANY DATABASE DDL TRIGGER
ANY DATABASE EVENT NOTIFICATION
ANY DATABASE EVENT SESSION
ANY DATABASE SCOPED CONFIGURATION
ANY DATASPACE
ANY EXTERNAL DATA SOURCE
ANY EXTERNAL FILE FORMAT
ANY FULL TEXT CATALOG
ALTER ANY MASK
ANY MESSAGE TYPE
ANY REMOTE SERVICE BINDING
ALTER ANY ROLE
ANY ROUTE
ANY SCHEMA
ANY SECURITY POLICY
ANY SERVICE
ANY SYMMETRIC KEY
ALTER ANY USER
AUTHENTICATE
BACKUP DATABASE
BACKUP LOG
CHECKPOINT
CONNECT
CONNECT REPLICATION
CONTROL
CREATE AGGREGATE
CREATE ASSEMBLY
CREATE ASYMMETRIC KEY
CREATE CERTIFICATE
CREATE CONTRACT
CREATE DATABASE
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE DEFAULT
CREATE A FULLTEXT CATALOG
CREATE FUNCTION
CREATE MESSAGE TYPE
CREATE PROCEDURE
CREATE QUEUE
CREATE REMOTE SERVICE BINDING
CREATE ROLE
CREATE ROUTE
CREATE RULE
CREATE SCHEMA
CREATE SERVICE
CREATE SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
DELETE
EXECUTE
EXECUTE ANY EXTERNAL SCRIPT
INSERT
KILL DATABASE CONNECTION
REFERENCES
SELECT
SHOWPLAN
SUBSCRIBE QUERY NOTIFICATIONS
TAKE OWNERSHIP
UNMASK
UPDATE
VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
VIEW ANY COLUMN MASTER KEY DEFINITION
VIEW DATABASE STATE
VIEW DEFINITION
You can get a list of all permissions on the SQL server by executing the command:
SELECT * FROM sys.fn_builtin_permissions('');
The result will look like this:
MORE NEWS
PreambleNoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...
PreambleMongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...
5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...
The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...
PreambleMS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...
First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...
PreambleAtom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...
PreambleMongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...
FlexibilityOne of the most advertised features of MongoDB is its flexibility. Flexibility, however, is a double-edged sword. More flexibility means more...
PreambleSQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...
PreambleWriting an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...
PreambleOracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...
PreambleIBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...
What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....
PreambleIf you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...
PreambleStarting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...