Database Security

SQL Server Security Modes

You can change the SQL Server authentication modes by right clicking on the Properties on the server and choosing security.

Server Instance vs. the Database

Databases within SQL Server are their own unique entities. SQL Server databases are designed to be easily detached from one server environment and reattached to another server without any extra work being done by a DBA.

Databases have their own users called database users and their own roles called database roles.

To authorize a SQL Server login access to a particular database, DBAs need to create a database user within the requested database, which maps to a SQL Server login. Database users are not shared among any databases on the server instance, but a single SQL Server login can map to one or more database users with each database user being in a different database.

Database Logins

Server Roles

At the server instance level, there are nine fixed server roles that you can assign to a SQL Server login. Fixed means that you, as a DBA, cannot create your own server roles; rather, you have only the nine to choose from. In SQL Server 2012, database administrators can create their own server level roles.

Before you start assigning logins to various roles, it is important to know that, in reality, you probably will use very few of these roles. The most popular is the sysadmin role. The functionality of these roles has been superceded with the introduction of server permissions. Server roles are still relevant to learn and use, because in some cases, it is not possible to create a SQL Server login with enough server permissions to mimic a server role.

DATABASE SECURITY

Database users are similar to SQL Server logins in that they are principals within the scope of a database. These users can be granted or denied specific permissions such as SELECT on a specific table. They can also be included in database roles. All database users are members of the public role. Thus, any permissions that are given to the public role will be in effect given to every database user.

Database Users

When you create a database, a few database users are created for you. One of them is dbo, which is the database owner; as the name implies, the role has permission to perform all activities within the database. Any member of the sysadmin fixed server role, who accesses a database is automatically mapped to the dbo user.

The guest database user is also always created. When SQL Server users log into a database where they do not have mappings, they are automatically mapped to the guest account. guest is created and is disabled by default. Thus, the default behavior for SQL Server logins that have no specific mapping is to not have any access at all to the database.

Schemas

When a database user is created, the user is assigned to a default schema. A schema is a logical collection of database objects. By grouping objects in a schema, a DBA can grant permissions to the schema that in turn would affect all the objects within the schema. If the user does not have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used.

Server Permissions

Before server permissions, if you wanted a login to be able to shut down the server, you needed to assign that login to the serveradmin role. Yet serveradmin imparts a number of other capabilities that you may not want to give that other user, such as the ability to change server-wide settings. With server permissions, you can now grant just the SHUTDOWN permission to a specific login, as shown in the following code:

USE master
GO
GRANT SHUTDOWN TO <login>
GO