SQL Server Security: Users
Database users are principals scoped at the database level. Database users establish a link between logins (which are stored at the server level) and users (which are stored at the database level). Database users are required to use the database and are also required to access any object stored in the database. Generally, the login name and database username are the same, but this is not a requirement. If desired, you could add a login named Chris and assign it to a user named Kayla. This type of naming convention would obviously cause some confusion and is not recommended, but SQL Server has the flexibility to allow you to do it. In addition, a user can be associated with a single person or a group of people. This capability is tied to the fact that a login can be related to a single account or group. For example, a login named training could be created and tied to a Windows group (that is, domain\training) that contains all the training personnel. This login could then be tied to a single database user. That single database user would control database access for all the users in the Windows group.
The relationship between logins and users can be broken when databases are moved or copied between servers. The reason is that a database user contains a reference to the associated login. Logins are referenced based on a unique identifier called a security identifier (SID). When a database is copied from one server to another, the users in that database contain references to logins that may not exist on the destination server or that may have different SIDs. You can use the sp_change_users_login system stored procedure to identify and fix these situations. You can run the following command against a newly restored or attached database to check for orphaned users:
EXEC sp_change_users_login ‘Report’
If orphaned users are shown in the results, you can rerun the procedure and fix the problems. For example, if the results indicate that a user named Chris is orphaned, you can run the following command to add a new login named Chris and tie the orphaned database user to this newly created login:
EXEC sp_change_users_login ‘Auto_Fix’, ‘Chris’, NULL, ‘pw’
Refer to SQL Server Books Online for full documentation on the sp_change_users_login system stored procedure. You can use the sys.database_principals catalog view to list all the users in a given database. The following example shows a SELECT statement using this view and the results from the SELECT:
left(u.name,25) AS [Name],
left(type_desc,15) as type_desc
sys.database_principals AS u
(u.type in (‘U’, ‘S’, ‘G’))
ORDER BY 1
/*Results fromprevious query
Name type type_desc
------------------------- ---- --------------
dbo S SQL_USER DBSVRXP\LocalUser1 U WINDOWS_USER guest S SQL_USER INFORMATION_SCHEMA S SQL_USER sys S SQL_USER */
dbo S SQL_USER
DBSVRXP\LocalUser1 U WINDOWS_USER
guest S SQL_USER
INFORMATION_SCHEMA S SQL_USER
sys S SQL_USER
Securing your database environment and providing the right type of access to your users are critical administrative tasks. This Page examines the security features in SQL Server 2008 and later version that relate to user administration and the objects that users can access.
An Overview of SQL Server Security
The SQL Server security model is the best place to start to understand SQL Server security. The model is based on three categories that separate the basic elements of security:
The results from the sys.server_principals selection include the name of the server principal as well as the type of principal. The rows that have a type_desc value of SQL_LOGIN, WINDOWS_GROUP, or WINDOWS_LOGIN are all logins established on the SQL Server instance. A login with a type_desc of SQL_LOGIN represents a login created with SQL Server authentication. Logins with a type_desc of WINDOWS_GROUP or WINDOWS_LOGIN are Windows groups or individual Windows users granted logins to SQL Server. The other entries with type_desc of SERVER_ROLE are fixed server roles discussed later in this page topic.
The logins established for Windows logins or groups can be part of the local domain of the SQL Server machine, or they can be part of another domain. In the previous example, DBSVRXP\LocalUser1 is a login established for a local user on a database server named DBSVRXP. The HOME\Administrator login is also a Windows login, but it is part of a network domain named HOME. Both logins are preceded by the domain that they are part of and are displayed this way in SQL Server.
In SQL Server 2000, logins were stored in the syslogins system table in the master database. The syslogins table is still available for selection as a view, but it is available only for backward compatibility. The catalog views (including sys.server_principals) are recommended for use instead.
You might have noticed in the earlier sys.server_principals output that two other logins are listed that we have not discussed yet. These logins (SA and NT AUTHORITY\SYSTEM) are system accounts installed by default at installation time. Each of these accounts serves a special purpose in SQL Server. The SA account is a SQL_LOGIN assigned to the sysadmin fixed server role. The SA account and members of the sysadmin fixed server role have permission to perform any activity within SQL Server. The SA account cannot be removed, and it can always be used to gain access to SQL Server. The SA account should always have a strong password to prevent malicious attacks, and it should be used only by database administrators. Users or logins requiring full administrative privileges can be assigned a separate SQL Server login that is assigned to the sysadmin fixed server role. This improves the audit trail and limits the amount of use on the SA account.
The NT AUTHORITY\SYSTEM login is an account related to the local system account under which SQL Server services can run. It is also added as a member of the sysadmin fixed server role and has full administrative privileges in SQL Server. This account can also be removed if the SQL Server services are not running with the local system account.
This should be done with caution, however, because it can affect applications such as Reporting Services. One other special account was not listed, but it would have been in SQL Server 2005. The BUILTIN\Administrators login is a Windows group that corresponds to the local administrators group for the machine that SQL Server is running on. The BUILTIN\Administrators group is no longer added by default as a SQL Server login during installation. In SQL Server 2005, it was also added as a member of the sysadmin fixed server role, but this is no longer the case. This change improves the security of SQL Server out of the box by limiting the number of people that have access (by default) to the SQL Server instance.
The BUILTIN\Administrators group can be manually added in SQL Server 2008 if desired. This allows domain administrators and anyone else who has been added to the local administrators group to have sysadmin privileges. Adding this group is not recommended but can be done if you want to set network privileges that are similar to past versions of SQL Server.
Principals are the entities that can request permission to SQL Server resources. They are made up of groups, individuals, or processes. Each principal has its own unique identifier on the server and is scoped at the Windows, server, or database level. The principals at the Windows level are Windows users or groups. The principals at the SQL Server level include SQL Server logins and server roles. The principals scoped at the database level include database users, data roles, and application roles.
Every principal granted security to SQL Server must have an associated login. The login provides access to SQL Server and can be associated with principals scoped at the Windows and server levels. These logins can be associated with Windows accounts, Windows groups, or SQL Server logins.
Logins are stored in the master database and can be granted permission to resources scoped at the server level. Logins provide the initial permission needed to access a SQL Server instance and allow you to grant access to the related databases. Permissions to specific database resources must be granted via a database user. The important point to remember is that logins and users are directly related to each other but are different entities. It is possible to create a new login without creating an associated database user, but a new database user must have an associated login. To better understand logins, you can look at the sys.server_principals catalog view. This view contains a row for every server-level principal, including each server login. The following example selects from this view and displays the results:
The implementation of the security model is relatively straightforward: you choose the principal from Column 1, the desired permission from Column 2, and the securable to assign the permission from Column 3. For example, a SQL LOGIN (the principal) needs to CREATE (the permission) databases (the securable). Together, these three elements represent a complete security assignment.
Some complexity has been introduced, based on the hierarchical nature of some of the security components. Security can be established on these hierarchical components, which in turn cascades the security to the underlying components. In addition, not all the permission components apply to every securable. Many of the securables have a select number of permissions that apply to them; conversely, many permissions apply only to a select number of securables. For example, SELECT permission is applicable to securables such as tables and views but would not be appropriate for stored procedures. The following sections discuss the tiers of the security model and their underlying components.
The first level of security encountered when accessing SQL Server is known as authentication. The authentication process performs the validation needed to allow a user or client machine to connect to SQL Server. This connection can be granted via a Windows login or SQL Server login.
Windows Authentication Mode
Windows Authentication mode validates the account name and password, using information stored in the Windows operating system. A Windows account or group must be established first, and then security can be established for that account in SQL Server. This mode has the advantage of providing a single login account and the capability to leverage domain security features, such as password length and expiration, account locking, encryption, and auditing. Microsoft recommends this approach.
Mixed Authentication Mode
Mixed authentication allows for both Windows authentication and SQL Server authentication. SQL Server authentication is based on a login that is created in SQL Server and lives in SQL Server only. No Windows account is involved with SQL Server authentication. The account and password are established and maintained in SQL Server. SQL Server logins can be created with stronger password enforcement that help better protect the login. This topic is discussed in more detail in the section “Managing SQL Server Logins,” later in this chapter. SQL Server authentication is useful in environments in which a Windows domain controller does not control network access. It can also be useful for Web applications or legacy applications, where it may be cumbersome to establish a Windows user account for every connection to the database server.\
Setting the Authentication Mode
You can select the authentication mode when you install SQL Server, and you can change it after the installation. To change the authentication mode after installation, you rightclick the server node in the Object Explorer and choose the Properties option. When the Server Properties dialog appears, you select the Security page (as shown in the pic below). The Security page allows you to specify Windows Authentication mode or SQL Server and Windows Authentication mode (that is, mixed authentication). Any changes to the authentication mode require a restart of SQL Server to make the change effective.
SQL DBA School is strongly committed to providing complete practical training exclusively on Microsoft SQL Server and Datawarehousing technologies. SQL DBA School Training Institute established in February 2013 is now one of the best training institute offering SQL Server and T-SQL (SQL Server) Training, SQL Database Administration (SQL DBA) Training and Business Intelligence (MSBI / SQL BI) Training.
We have been working with numerous consultancies in USA & UK. We undertake training on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 versions. We make sure that all our sessions are very much interactive and well structured with doubts clarifications during and after each course.
"An education equips a mind. A degree demonstrates its potential."
- IGNATIUS J. REILLY
"SQL DBA School presented me with all of the opportunities I needed to succeed."
- GEORGE BOWLING
"The sense of accomplishment I felt after completing my SQL DBA School courses was great!"
- TIM DOYLE