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.
Users pass through two stages of security when working in SQL Server:
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).
Authentication identifies the user's login account and verifies the ability to connect to an instance of SQL Server.
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.
Authorization identifies the activities a user is allowed to perform in the database.
SQL Server authentication:
Authentication modes (under Windows):
Windows authentication mode
sp_grantlogin allows a Windows user or group account to connect to Microsoft SQL Server using Windows Authentication.
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'
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.
Fixed server roles exist outside of individual databases. They are managed independently of user databases.
Fixed database roles