Programming4us
         
 
 
SQL Server

SQL Server 2008: Security and User Administration - Managing Principals (part 1) - Users

10/17/2010 6:14:37 PM
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.

Logins

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:

select left(name,25) name, type, type_desc
from sys.server_principals AS log
WHERE (log.type in ('U', 'G', 'S', 'R'))
order by 3,1

/*Results from previous query
name type type_desc
------------------------- ---- ------------
bulkadmin R SERVER_ROLE
dbcreator R SERVER_ROLE
diskadmin R SERVER_ROLE
processadmin R SERVER_ROLE
public R SERVER_ROLE
securityadmin R SERVER_ROLE
serveradmin R SERVER_ROLE
setupadmin R SERVER_ROLE
sysadmin R SERVER_ROLE
sa S SQL_LOGIN
DBSVRXP\LocalUser1 U WINDOWS_LOGIN
HOME\Administrator U WINDOWS_LOGIN
NT AUTHORITY\SYSTEM U WINDOWS_LOGIN
*/

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_LOGINtype_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 chapter. represents a login created with SQL Server authentication. Logins with a

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.

Note

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.

Note

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.


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.

Tip

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:

SELECT
left(u.name,25) AS [Name],
type,
left(type_desc,15) as type_desc
FROM
sys.database_principals AS u
WHERE
(u.type in ('U', 'S', 'G'))
ORDER BY 1

/*Results from previous 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
*/

The SELECT statement in this example returns five rows (that is, five users). This SELECT was run against the AdventureWorks2008 database, and the only user explicitly added to the database was the Windows user DBSVRXP\LocalUser1. The other users are special users who are added by default to each database. These users do not have corresponding server logins named the same. These users are discussed in the following sections.

The dbo User

The dbo user is the database owner and cannot be deleted from the database. Members of the sysadmin server role are mapped to the dbo user in each database, which allows them to administer all databases. Objects owned by dbo that are part of the dbo schema can be referenced by the object name alone. When an object is referenced without a schema name, SQL Server first looks for the object in the default schema for the user that is connected. If the object is not in the user’s default schema, the object is retrieved from the dbo schema. Users can have a default schema that is set to dbo.

Schemas and their relationship to users are discussed in more detail in the section “User/Schema Separation,” later in this chapter.

The guest User

The guest user is created by default in each database when the database is created. This account allows users that do not have a user account in the database to access the database. By default, the guest user does not have permission to connect to the database. To allow logins without a specific user account to connect to the database, you need to grant CONNECT permission to the guest account. You can run the following command in the target database to grant the CONNECT permission:

GRANT CONNECT TO GUEST

When the guest account is granted CONNECT permission, any login can use the database. This opens a possible security hole. The default permissions for the guestguest account, and all logins that use it will be granted those permissions. Generally, you should create new database users and grant permissions to these users instead of using the guest account. account are limited by design. You can change the permissions for the

If you want to lock down the guest account, you can. You cannot drop the guest user, but you can disable it by revoking its CONNECT permission. The following example demonstrates how to revoke the CONNECT permission for the guest user:

REVOKE CONNECT FROM guest

If you decide to grant additional access to the guest account, you should do so with caution. The guest account can be used as a means for attacking your database.

The INFORMATION_SCHEMA User

The INFORMATION_SCHEMA user owns all the information schema views installed in each database. These views provide an internal view of the SQL Server metadata that is independent of the underlying system tables. Some examples of these views include INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.CHECK_CONSTRAINTS. The INFORMATION_SCHEMA user cannot be dropped from the database.

The sys User

The sys account gives users access to system objects such as system tables, system views, extended stored procedures, and other objects that are part of the system catalog. The sys user owns these objects. Like the INFORMATION_SCHEMA user, it cannot be dropped from the database.

Tip

If you are interested in viewing the specific objects owned by any of the special users discussed in these sections, you can use a SELECT statement like the following:

--Find all objects owned by a given user
SELECT name, object_id, schema_id, type_desc
FROM sys.all_objects
WHERE OBJECTPROPERTYEX(object_id, N'OwnerId') = USER_ID(N'sys')
ORDER BY 1

The SELECT in this example shows all the objects owned by the sys user. To change the user, you simply change the parameter of the USER_ID function in the SELECT statement from 'sys' to whatever user you want.


User/Schema Separation

The changes to schema security introduced in SQL Server 2005 have been carried forward to SQL Server 2008. Versions of SQL Server before SQL Server 2005 had schemas, but they did not conform to the American National Standards Institute (ANSI) definition of schemas. ANSI defines a schema as a collection of database objects that one user owns and that forms a single namespace. A single namespace is one in which each object name is unique and there are no duplicates. So, for example, if you have two tables named customer, they cannot exist in the same namespace.

To fully understand the user/schema changes in SQL Server 2008, you need to understand how schemas were used in prior versions of SQL Server. In SQL Server 7.0 and 2000, a default schema was created for each user, and it had the same name as the user. For example, if you created a new user named Rachael, a corresponding schema named Rachael would be created as well. There was no option in those releases to change the default schema for a user, and each user was forever bound to a schema with the same name. When the user created new objects, the objects were created by default in that user’s schema, which is always the name of the user. So, if Rachael created an object named customer, it was placed in the Rachael schema, and the object was owned by Rachael. When Rachaeldatabase.owner.object. If a linked server was used, according to the SQL Server 2000 documentation, the object in the linked server could be referenced with the four-part name linked_server.catalog.schema.object. (for example myserver.AdventureWorks2008.Rachael.Customer). You can see that the schema name is used prior to the object name when the object is outside the local server. The bottom line is that the schema and owner were basically the same thing in SQL Server 7.0 and 2000. wanted to reference the object, she could use a three-part name with the format

With SQL Server 2005 and SQL Server 2008, the owner and schema have been separated. This is made possible in part by allowing a database user to have a default schema different from the name of the user. For example, our sample user Rachael could be assigned the default schema Sales. When Rachael creates objects in the database, her objects are created, by default, in the Sales schema. If Rachael wants to reference an object that she created, she can reference the table in a number of different ways. She can use the full four-part name (server.database.schema.object) that includes the Sales schema name to reference the object via a linked server. She can simply refer to the object with the object name alone, and the Sales schema will be searched first for the object. She can also use a three-part name or a two part name. If the object name is not found in the Sales schema, the dbo schema will be searched. This concept is illustrated in the following sample SELECT statements that all retrieve the same rows from the Region table that was created by Rachael in the Adventureworks2008 database.

select * from region
select * from sales.region
select * from AdventureWorks2008.Sales.Region

The important point to remember is that owners and schemas are different from one another in SQL Server 2008. For example, you can have a customer table created in the Sales schema, and that table can be owned by a user named Chris. The object should be referenced with the schema name qualifier, such as Sales.Customer, not Chris.Customer. This has the distinct advantage of allowing object ownership to change without affecting the code that references the object. The reason is that database code that references an object uses the schema name instead of the object owner.

The schema enhancements in SQL Server 2008 go well beyond the user/schema separation. Schemas are an integral part of all the database objects that exist in SQL Server. As we delve into more details about SQL Server security and the assignment of permissions, you will see that schemas play a very important part.

Other -----------------
- SQL Server 2008: Security and User Administration - Managing Securables
- SQL Server 2008: Security and User Administration - Managing Permissions
- SQL Server 2008: Security and User Administration - Managing SQL Server Logins
- Managing SQL Server Permissions (part 4) - Using T-SQL to Manage Permissions
- Managing SQL Server Permissions (part 2) - Using SSMS to Manage Permissions at the Object Level
- Managing SQL Server Permissions (part 2) - Using SSMS to Manage Permissions at the Database Level
- Managing SQL Server Permissions (part 1) - Using SSMS to Manage Permissions at the Server Level
- Central Management Servers (part 4) - Evaluating Policies
- Central Management Servers (part 3) - Configuring Multi-Server Query Options
- Central Management Servers (part 2) - Running Multi-Server Queries
- Central Management Servers (part 1) - Creating a Central Management Server
- SQL Server 2008 : The sqlcmd Command-Line Utility
- Installing SQL Server 2008 Using a Configuration File
- SQL Server 2008 : Slipstream Installations
- SQL Server Programmability Objects
- SQL Server 2005 : Data Querying and Reporting (part 2)
- SQL Server 2005 : Data Querying and Reporting (part 1)
- Configuring SQL Server 2008 : Instances vs Default Instance
- sp_configure and SQL Server Management Studio
- Configuring SQL Server 2008 : Database Mail
 
 
Most View
- Configuring Windows 7 for Secure Networking
- Exchange Server 2010 : Useful Tools for an Upgrade (part 1)
- SQL Server 2005 : Performing Database Backups
- Windows 7 : Installing Internet Information Services
- iPad SDK : Keyboard Extensions and Replacements (part 1) - Adding a Keyboard Button in Dudel
- Managing Windows Server 2012 Storage and File Systems : Storage Management (part 6) - Configuring storage - Using the MBR and GPT partition styles, Using the disk storage types
- Windows Server 2008 Server Core : Accessing DLLs Using the RunDLL32 Utility
- Windows Phone 7 : Setting Up E-Mail and Your Calendar
- Windows 7 : Checking Your Computer’s Security Settings (part 1)
- iPad SDK : Popovers - Popover Preparations
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS