Database Engine Permission Basics

I am posting this on behalf of my colleague Rick Byham, a technical writer on the SQL Server Team.

Database Engine permissions are managed at the server level through logins and fixed server roles, and at the database level through database users and user-defined database roles.

Logins

Logins are individual user accounts for logging on to the SQL Server Database Engine. SQL Server supports logins based on Windows authentication and logins based on SQL Server authentication. For information about the two types of logins, see Choosing an Authentication Mode .

Fixed Server Roles

Fixed server roles are a set of preconfigured roles that provide convenient group of server-level permissions. Logins can be added to the roles using the sp_addsrvrolemember procedure.

Database Users

Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the same as the login name, though it does not have to be the same. Each database user maps to a single login. A login can be mapped to only one user in a database, but can be mapped as a database user in several different databases.

Fixed Database Roles

Fixed database roles are a set of preconfigured roles that provide convenient group of database-level permissions. Database users and user-defined database roles can be added to the fixed database roles using the sp_addrolemember procedure.

User-defined Database Roles

Users with the CREATE ROLE permission can create new user-defined database roles to represent groups of users with common permissions. Typically permissions are granted or denied to the entire role, simplifying permissions management and monitoring.

Typical Scenario

The following example represents a common and recommended method of configuring permissions.

Read more: SQL Server Security
QR:

Posted via email from Jasper-Net