Understanding SQL Server Security

Working with Permissions:

·         In order to allow the users to work with required objects we need to

•    Either grant the permissions to the users
•   Or Add the user(s) to required ROLE

SQL Server supports 3 types of permissions

1.  DATABASE Level
2.  SCHEMA Level
3. OBJECT Level

To work with permissions we need

·         GRANT ( To grant permissions)

·         REVOKE (To take back permission)

·         DENY (To restrict other users should not grant permissions)

1. Database Level:

·         Below are the database level privileges

•       Backup database
•         Create table
•         Show plan
•         Alter table
•       Select, insert, update e.t.c.,

Syntax: Grant ….. to <UserName/RoleName> [WITH GRANT OPTION]

Ex :  Grant select, backup database , insert to User_Name

2. Schema Level Permission:

·       To group similar table in a database we can create schemas

·       By default every table is stored in ‘dbo’ schema

·       Schema provides the following advantages

•         To group similar tables
•         To grant permission on more than one table at once.
•         No need to change the owner of the table(s) if the user is deleted

Syntax to grant schema level permission

GRANT  ….. ON SCHEMA::[schemaName] to user/role

Ex: GRANT SELECT ON schema::[sales] to user_Name

3.   Object Level Permission:

·       To Grant permissions on individual objects like tables, views etc.

Syntax to grant object level permission

GRANT  ….. ON <objectName> to <user/role>

Ex: GRANT SELECT, UPDATE, DELETE on EMP(empid,ename) TO John

DATABASE ROLES:

·       Role is used to group a set of privileges.

·       We can reduce the process of granting and taking back permission to large number of users with roles.

·       SQL Server supports 3 types of roles

o  Fixed Database Roles

o  Custom Database Roles

o  Application Roles

Fixed-Database Roles

The following table shows the fixed-database roles and their capabilities. These roles exist in all databases. The permissions assigned to the fixed-database roles cannot be changed. The permissions assigned to the fixed-database roles cannot be changed. The following figure shows the permissions assigned to the fixed-database 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 SQL Data Warehouse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadmin	Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
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.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter	Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader	Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

Custom-Database Roles

          We can create role with required privileges in order to assign a group of people related privileges.

We can reduce the process of granting and taking backup permissions to large number of users with custom roles.

Steps :

1.       Creating Role

Syntax : Create Role <RoleName>

2.       Granting permissions to the role

Syntax : GRANT …. TO <RoleName>

3.       Adding users to the role using sp_addrolemember

Example : Create role with the name CustomerCare_Role

          Grant select, insert on emp table – Object Level

                Grant select on library schema - Schema Level

                Grant backup database permission – database level

1.     Creating Role

Create Role CustomerCare_Role

2.     Granting permissions to the role

Grant select, insert on emp to CustomerCare_Role

Grant select on Schema::[Library] to CustomerCare_Role

Grant backup database to CustomerCare_Role

3.     Granting permissions to the role

Sp_addrolemember @rolename=’CustomerCare_Role’, @membername=’<User_Name>’