Administering SQL Server Security

SQL Server supports two modes for validating connections and authenticating access to database resources:

1. Windows Authentication mode and
2. SQL Server and Windows Authentication mode

Both provide the ability for users to authenticate to SQL Server and access resources.

SQL Server logins and Users
Although the terms login and user are often used interchangeably, they are very different.

• A login is used for user authentication
• A database user account is used for database access and permissions validation.

Administering SQL Server Security

Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called “authentication”. This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server.

The login cannot be given permissions for database securable objects directly. The login must be mapped to a database user; the database user then can be given permissions on database-scoped securable objects either directly or through roles and schemas.

Role-Based Access
Although database users can be given permissions on objects directly, this is generally considered a bad practice when dealing with complex security permissions. It is much more effective to create roles for each type of user and assign the correct permissions to the role. Role-based access reduces the
cost of ongoing security administration because users can be added and removed from roles without your having to re-create complex permissions for each user.

Server Level Roles

Server Role Default Permissions
bulkadmin Granted: ADMINISTER BULK OPERATIONS
dbcreator Granted: CREATE DATABASE
diskadmin Granted: ALTER RESOURCES
processadmin Granted: ALTER ANY CONNECTION, ALTER SERVER STATE
securityadmin Granted: ALTER ANY LOGIN
serveradmin Granted: ALTER ANY ENDPOINT, ALTER RESOURCES,ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN,ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN,VIEW SERVER STATE
setupadmin Granted: ALTER ANY LINKED SERVER
sysadmin Granted with GRANT option: CONTROL SERVER
public Granted: VIEW ANY DATABASE

Note
All logins belong to the public server role by default. The public role is granted VIEW ANY DATABASE by default.

Database Level Roles

Database RoleDefault Permissions
db_accessadminGranted: ALTER ANY USER, CREATE SCHEMA
db_accessadminGranted with GRANT option: CONNECT
db_backupoperatorGranted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareaderGranted: SELECT
db_datawriterGranted: DELETE, INSERT, UPDATE
db_ddladminGranted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA,
db_denydatareaderDenied: SELECT
db_denydatawriterDenied: DELETE, INSERT, UPDATE
db_ownerGranted with GRANT option: CONTROL
db_securityadminGranted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
publicGranted: SELECT on system views

Note
All database users belong to the public database role by default. It is a best practice to avoid using the public database role when assigning permissions.


<<Click here to see all posts>>

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.