Terms

Roles are groups of users organized for administrative purposes. They are created in SQL Server when an equivalent Windows group does not exist. Roles are only applicable at the database level. Roles (and user accounts) identify users within the database and control access, permissions, and ownership to database objects.

Login allows users to access to SQL Server.

A security plan identifies which users can see which data and perform which activities in the database.

The users-to-activity map lists users and the activities they need to perform.

Security Levels

Users pass through two stages of security when working in SQL Server:

• AUTHENTICATION (login authentication)
• AUTHORIZATION (permission validation)

Authentication (login authentication) establishes the user's ability to connect to the Server. If authentication is successful, the user connects to an instance of SQL Server (not to the database).

Authorization (permission validation stage): a user needs permissions to access databases on the server. The permissions validation stage controls the activities the user is allowed to perform in the SQL Server database. The login then has to be mapped to a SQL Server user account, which is used to control activities performed in the database.

Authentication types

Windows authentication:

• Windows account or group controls user access to SQL Server
• Users do not provide login and password when they connect to the SQL Server, instead they open trusted connection
• Windows user account and groups can be defined as SQL Server login accounts

SQL Server authentication:

• Requires a SQL server login account and a password
• Users must supply both the SQL Server login and the password

Authentication modes (under Windows):

• Windows authentication mode - Allows only Windows authentication (only clients that support trusted connections can connect to the server)
• Mixed authentication mode - Allows users to connect to SQL Server using Windows authentication or SQL Server authentication. Enables access to the SQL Server for non-Windows, Internet, and mixed group clients.

Windows authentication mode

sp_grantlogin allows a Windows user or group account to connect to Microsoft SQL Server using Windows Authentication.

Syntax:

sp_grantlogin [@loginame =] 'login'

Example: Create a new login (Windows User type) and assign a default database:

sp_grantlogin 'OFFICE\McFerry'
GO
sp_defaultdb 'OFFICE\McFerry', 'Lottery'
GO

Example: Add permission to access the current database:

EXEC sp_grantdbaccess 'Office\McBurak' 

Roles

Create a new role:

sp_addrole 'RoleName', [owner OF the NEW ROLE - DEFAULT: dbo]

Granting permissions to a role:

GRANT SELECT, UPDATE, INSERT ON TableName | ViewName TO RoleName

Add a user to a role:

sp_addrolemember 'RoleName', 'OFFICE\McDuda'

Fixed server roles

Server roles provide groupings of administrative privileges at the server level. Server roles cannot be created.

• dbcreator (Database creators) - Create and alter databases
• diskadmin (Disk administrator) - Manage the disk files
• processadmin (Process administrators) - Manage SQL Server processes
• securityadmin (Security administrator) - Manage and audit server logins
• serveradmin (Server administrator) - Configure server-wide settings
• setupadmin (Setup administrator) - Install replication
• sysadmin - span all of other fixed server roles

• sp_helpsrvrole - Returns a list of the fixed server roles with descriptions.
• sp_srvrolepermission - Returns the permissions applied to each fixed server role.

Fixed database roles

• public - Maintain all default permissions for users in a database
• db_owner - All permissions in the database (spans all of the other fixed database roles)
• db_accessadmin - Add or remove database users, groups, and roles
• db_ddladmin - Add, modify, or drop database objects
• db_datareader - Read data from any table
• db_datawriter - Add, change, or delete data from all tables
• db_securityadmin

• sp_helpdbfixedrole - Displays a list of all fixed database roles.
• sp_dbfixedrolepermission - Displays the permissions for each fixed database role.