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.
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.
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|
All logins belong to the public server role by default. The public role is granted VIEW ANY DATABASE by default.
Database Level Roles
|Database Role||Default Permissions|
|db_accessadmin||Granted: ALTER ANY USER, CREATE SCHEMA|
|db_accessadmin||Granted with GRANT option: CONNECT|
|db_backupoperator||Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT|
|db_datawriter||Granted: DELETE, INSERT, UPDATE|
|db_ddladmin||Granted: 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_denydatawriter||Denied: DELETE, INSERT, UPDATE|
|db_owner||Granted with GRANT option: CONTROL|
|db_securityadmin||Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION|
|public||Granted: SELECT on system views|
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.