Basics of SQL Server Architecture

SQL Server is a client/server relational database management system. Figure 1.3 shows the process that every query must follow, from its origin as a SQL query in the client application running in the client computer, to the final result set received by the client application.

These steps are defined as follows:

These are some important points to consider about this process:

• The client application sends only a database request to the server computer.

• SQL Server sends only the final result set to the client application, saving network bandwidth.

• SQL Server receives the SQL request and sends result sets in return. SQL Server does not spend server resources on user interaction.

• SQL Server is not responsible for the final output format; it is the responsibility of the client application. The client application does not use client resources to solve low-level query solving and data access processes.

• The client application can be designed independently of the database system used in the back end. Data access operations are based in a high-level data access library, which can be easily changed to connect to other types of database systems.

• The client application is not aware of the network protocol used to connect to the server, and this protocol can be changed at any time, provided that the server and client share a common protocol .

When you install SQL Server 2000, you install two different sets of components:

• Server components are back-end services, responsible for data storage, data integrity, security, concurrency, and so on.

• Client components are front-end applications, used by administrators, developers, and even end users, to administer, develop, test, and use a SQL Server 2000 database system.

Server Components

What we call SQL Server 2000 is actually a collection of several Windows services:

• Microsoft SQL Server service (MSSQLServer)—The main service, responsible for data storage, data integrity, consistency, concurrency, security, query processing, optimization, and execution.

• Microsoft SQL Server Agent (SQLServerAgent)—Responsible for scheduling jobs, managing alerts, and Notifying operators. SQL Server Agent is an important service in SQL Server Administration because so many administrative operations depend on it to be executed automatically at fixed intervals—for example, backups, data consistency checks, rebuilding indexes, importing and exporting data, replication, and so on.

• Microsoft Search—Provides full-text search capabilities to SQL Server, as well as to Microsoft Exchange and Index Server.

• Microsoft SQL Server OLAP Service—Provides back-end support for Analysis Services.

• Microsoft Distributed Transaction Coordinator (MS-DTC)—Provides transaction support in multiserver and heterogeneous environments.

• Server Network libraries—SQL Server can listen to several network libraries at the same time, waiting for queries to answer, and use any of these libraries to send results to the client. The selected database network library must have a compatible server network library to work with. SQL Server 2000 currently supports the following network libraries: TCP/IP Sockets, Named Pipes, Multiprotocol, NWLink IPX/SPX, VIA ServerNET II SAN, VIA GigaNet SAN, Banyan VINES, and AppleTalk ADSP.

Microsoft SQL Server Service contains different components that collaborate to provide back-end data services. The three main components are

• Open Data Services—This component receives client requests from the network library and passes them on to SQL Server. When SQL Server terminates the query process, it sends the result set to ODS to be transferred through the network to the client application.

• Relational Engine—This component is responsible for parsing, optimizing, executing queries, and enforcing security.

• Storage Engine—This component manages physical storage operations, such as data storage, allocation and deallocation of data pages, transaction logging and recovery, database backups and restoring, locking, and so on.

Client Tools

The preceding section discussed the different server components. In the client side, we can identify the following components:

• The client application—This application, developed using any programming language, provides user interaction, prepares requests to the database server, and shows query results to the user in a user-friendly way.

• Database library— This library is responsible for translating application requests into specific statements that the database server can understand.

• Client network libraries—This is the component that talks to the server network library to send and receive TDS packets through the network.

Figure 1.4 shows the different database libraries you can use to connect a client application to SQL Server 2000:

• Direct HTTP access from an HTML page or an Active Server Page (ASP)—In this case, you use the SQL ISAPI extension through a virtual directory in Internet Information Server, supporting direct XPath queries through HTTP and XML input/output.

• Native SQL Server . DB-Library access—This is not the recommended solution because most of the new SQL Server 2000 functionality is not exposed through DB-Library.

• Access to the ODBC API—Using any programming language, although C or C++ is recommended.

• Indirect access to the ODBC library through Remote Data Objects (RDO)—This solution provides an object-oriented library that encapsulates database access objects. RDO is maintained for backward compatibility with existing applications, but Microsoft recommends ActiveX Data Objects instead.

• Direct access to the OLE DB Provider library using C++ or C# is recommended—This solution provides full access to SQL Server 2000 through a comprehensive data-access object model with specific properties available in the native SQL Server OLE DB provider.

• Indirect access to the OLE DB provider for SQL Server through ActiveX Data Objects (ADO) or ADO.Net—ADO exposes an object model, as OLE DB does, easier to implement than direct access to the OLE DB provider, and is suitable to any programming and scripting language, including any version of Visual Basic and ASP.Net.

• Indirect access to the SQL Server ODBC driver through the OLE DB provider with or without the ADO library—This solution is not recommended because of the extra steps involved in the data access, unless specific ODBC functionality is required.

• Access to database metadata through ADOX and OLE DB—This is an alternative way to connect to SQL Server, to send Data Definition Language (DML) statements, and metadata discovery. This connectivity solution is not represented in the diagram because it is not a very common solution.

• Administrative access to SQL Server through SQL-DMO (Distributed Management Objects)—This is the object model library that SQL Server Enterprise Manager uses to connect to SQL Server. Developers can use all this functionality to build small administration applications, as subsets of what Enterprise Manager can do.

• Windows Management Instrumentation (WMI)—WMI is a scalable Windows 2000 component, common to other server applications, which exposes an object model to control and administer SQL Server, as well as other server services and devices .

SQL Server 2000 includes some client applications you can use to administer and develop databases:

• Enterprise Manager

• Query Analyzer

• Profiler

• Upgrade Wizard

• Service Manager

• Command-line utilities

Enterprise Manager

You can use Enterprise Manager to manage any SQL Server 2000 instance, including the default SQL Server 2000 instance, running locally or remotely. You also can use Enterprise Manager to manage any local or remote SQL Server 7.0 installation. However, this version of Enterprise Manager is not compatible with SQL Server 6.5.

Figure 1.5 shows the Enterprise Manager environment, similar to the well-known Windows Explorer interface, in which you can identify different sections for every server:

• The SQL Server Administration Tree—This panel uses the TreeView control to display the structure of every registered server. It displays different icons for every database object and shows context menus for every object, according to the methods that can be applied to each specific object.

• The menu bar—In this menu bar, you can find the Action menu, which is equivalent to the object context menu available from the tree; the View menu to specify how to display information about the selected object in the tree; and the Tools menu to show general commands you can use in Enterprise Manager to administer SQL Server.

• The taskbar—This is a dynamic list of icons that represents processes you can run in the current context, as well as navigation keys through the tree.

• The information panel—This panel shows information in different formats, depending on the selected object. Figure 1.5 shows the Taskpad, which, in this case, is an HTML page with information about the Northwind database.

For every server, the Enterprise Manager tree shows the following sections (folders):

• Databases—This section includes the list of available databases in the connected server.

• Data Transformation Services— This section gives you access to the DTS Designer and the Import/Export Wizard.

• Management—Enter this section to see the current activity of the connected server; to access SQL Server Agent objects, alerts, jobs, and operators; to manage backups and database maintenance plans; and to look at the SQL Server event log.

• Replication—This is where you can administer publications and subscriptions, if this server publishes or subscribes to any database.

• Replication Monitor—This section is available only if Replication is installed on this server. In that case, you can use this section to monitor and administer replication agents.

• Security—This section gives you access to the administration of SQL Server logins, server roles, linked servers, and remote servers. Later in this chapter, in the "Security Model" section, you will learn about SQL Server security and Chapter 15, "Working with Heterogeneous Environments: Setting Up Linked Servers," covers linked and remote servers.

• Support Services—Access this section to administer other services, such as Distributed Transaction Coordinator, Full-Text Search, and SQL Mail.

• Meta Data Services—This section gives you access to the Microsoft Repository.

SQL Server Enterprise Manager provides wizards to perform most of the standard administrative activities. These wizards are available from the Tools menu and from the Taskpad, at server and database level. Figure 1.6 shows the Wizards list from the Taskpad. To access the Taskpad, select a server in the Enterprise Manager tree, and in the View menu, select Taskpad. To show the wizards, click on the Wizard tab in the Taskpad.

From SQL Server Enterprise Manager, you can design a database in a similar way to Visual Database Tools (from Visual Studio Enterprise Edition). Figure 1.7 shows the Northwind database diagram. You can create this diagram by opening the list of databases and opening the subtree for the Northwind database. There you can right-click on Diagrams and select New Database Diagram. This menu will open the Create Database Diagram Wizard that will lead you step-by-step through the creation of this diagram.

Query Analyzer

Query Analyzer is a client tool designed to send queries to SQL Server and to display results. This is not an end-user application; instead, it is a developer tool used to manage databases and create database applications through the use of Transact-SQL scripts.

Query Analyzer is the tool you will use throughout this book to practice the examples. Figure 1.8 shows Query Analyzer after retrieving a query result set from SQL Server.

Appendix B gives you a comprehensive coverage of Query Analyzer.

Profiler

SQL Server Profiler is a client tool that captures SQL Server activity and sends this activity to a file, database table, or the screen, giving you a powerful analysis tool. For every trace you can

• Select which events to trace.

• Select which information to show for every event.

• Select how to group the events.

• Apply filters to include or exclude specific values, such as applications, logins, databases, hosts, and so on.

• Save the trace in a trace file or a database table.

Using Profiler, you can

• Monitor real-time activity in SQL Server.

• Detect long-running queries.

• Trace locks and deadlocks.

• Summarize activity per database, user, host, and so on.

• Select which database objects are more heavily used to prioritize optimization decisions.

• Detect actual SQL Server activity from applications in which the source code is not available. You can create a trace to look at what Enterprise Manager does when you administer SQL Server using graphical user interface commands.

• Monitor database autogrowth or autoshrink.

• Perform security audits.

To start a trace, follow these instructions:

The Upgrade Wizard

The SQL Server Upgrade Wizard converts SQL Server 6.5 databases to the SQL Server 2000 format. You can upgrade the entire server or selected databases. The upgrade process will transfer and convert the database catalog, most of the server and database settings, and user data.

Using the SQL Server Upgrade Wizard, you can upgrade a SQL Server 6.5 database to the default SQL Server 2000 instance running in the same computer. In this case, you can use a tape device to avoid space problems in the hard disk. However, it is more efficient to upgrade from one computer to another in the same network.

You can find the SQL Server Upgrade Wizard in the Microsoft SQL Server—Switch programs group. Figure 1.10 shows the main form of this wizard, right after the Wizard Welcome form.

Service Manager

Using Service Manager, you can start, pause, and stop SQL Server services on any available SQL Server in your network. Figure 1.11 shows the SQL Server Service Manager. There you can specify to autostart a service whenever the operating system starts.

When you stop, the SQL Server Service, SQL Server

When you pause SQL Server, you only prevent new connections, but existing users can continue their work. This gives you the opportunity to send a message to the connected users, so they can finish their work before stopping SQL Server.

Command-Line Utilities

SQL Server 2000 setup installs several utilities that can be started from the command prompt. To use them, you must open a command prompt window and type any of the following commands:

• bcp—Bulk Copy Program. Use this utility to import or export data to and from SQL Server 2000. Chapter 14, "Transferring Data to and from SQL Server," contains information on how to use bcp.

• console—Displays backup and restore messages when the operation uses a tape device.

• dtsrun—This utility runs Data Transformation Packages from the command prompt.

• dtswiz—Use this utility to start the DTS Import/Export Wizard.

• isql—This is a query tool that uses DB-Library to connect to SQL Server. Use this tool to execute scripts in SQL Server 2000 that do not require user interaction, such as administrative scripts. You can send the output to a file.

• isqlw—Use this command to run SQL Query Analyzer.

• osql—This is a similar tool to isql, but it uses ODBC to connect to SQL Server.

• itwiz—Runs the Index Tuning Wizard, which will advise you about the best strategy to tune your database. Chapter 6, "Optimizing Access to Data: Indexes," teaches you how to use the Index Tuning Wizard.

• makepipe—This utility creates a pipe that helps you test the Named Pipes protocol with the readpipe utility.

• readpipe—This utility reads from a pipe created using the makepipe utility.

• odbccmpt—Enables or disables the compatibility flag for ODBC applications, which solves some compatibility problems related to the ODBC 3.7 drivers.

• odbcping—Tests connectivity to an ODBC data source.

• rebuildm—Rebuild Master utility. This utility rebuilds all the system databases.

• distrib—Configures and runs the Replication Distribution Agent.

• logread—Configures and runs the Replication Log Reader Agent.

• replmerg—Configures and runs the Replication Merge Agent.

• queueread—Configures and runs the Replication Queue Reader Agent.

• snapshot—Configures and runs the Replication Snapshot Agent.

• scm—Service Control Manager. This utility is the command-line version of the SQL Server Service Manager, with extra functionality.

• sqlagent—Starts the SQLServerAgent service.

• sqldiag—Produces a full diagnostics report about SQL Server current environment and activity.

• sqlftwiz—Starts the Full-text Indexing Wizard.

• sqlmaint—Runs specific database maintenance tasks.

• sqlservr—Starts, stops, or pauses any instanceof SQL Server 2000.

• vswitch—Switches the default instance of SQL Server between SQL Server 6.5 and SQL Server 2000.

Database Components (Objects)

A SQL Server 2000 database contains different types of objects. Some objects contain user data, whereas other objects are just definitions of objects, business rules declarations, and programs.

Data is arranged in tables and every field, identified by its name and data type, represents a different attribute. Tables are the main database objects because you store your data in tables. You will learn how to create tables in Chapter 3, "Working with Tables and Views."

Every individual value uses a specific data type. SQL Server provides a collection of data types, compatible with the ANSI SQL 92 standard, but you can create your own user-defined data types, based on existing system supplied data types. Chapter 2, "Elements of Transact-SQL," teaches you how to use data types and how to define and apply user-defined data types.

To guarantee data integrity and consistency, you can define constraints in the following manner:

• Primary key and unique constraints provide entity integrity, maintaining uniqueness in one or more columns.

• Check and default constraints maintain domain integrity, checking for specific business rules to apply to the inserted data.

• Foreign keys maintain referential integrity, maintaining links between related information in different tables.

Chapter 7, "Enforcing Data Integrity," covers constraints in detail, as well as Rule and Default objects.

Complex queries can be defined as views, which can be reused in other queries, providing better readability and easier maintenance. You learn about views in Chapter 3, "Working with Tables and Views."

To speed up access to data, you can create indexes on tables and views. Indexes store subsets of the available information in an ordered way, as keys and pointers to the actual data, to provide fast access to the data. Chapter 6, "Optimizing Access to Data: Indexes," discusses indexes in detail.

You can expand the SQL Server capabilities creating user-defined functions. These functions can be as simple as a scalar function or as complex as a multistatement table-valued user-defined function. To know more about user-defined functions, read Chapter 10, "Enhancing Business Logic: User-Defined Functions (UDF)."

Complex processes can be defined as stored procedures. In this way, SQL Server can optimize the stored procedure's execution plan on the first execution, and reuse this optimized execution plan for every further call. Chapter 8, "Implementing Business Logic: Programming Stored Procedures," teaches you how to create and use stored procedures.

You can define special stored procedures, called triggers, which, linked to a table, execute automatically whenever you attempt any data modification to the table .

Security Model

SQL Server stores important data for your company, and you want to guarantee that every user can access only the data she needs and with the appropriate access rights.

Ensuring a proper security policy in a SQL Server database is a task that starts in your IT environment. To plan this security policy, you should find answers to the following questions:

• Who can access your company premises?

• Who can physically access your corporate servers?

• Who, and from where, can connect to your corporate network?

• Do you apply proper password policies in your network?

• Do you isolate sensitive servers in restricted networks?

• Do you follow adequate security auditing policies?

Your network is secure if you can identify and ensure

• What resources need shared access by nonadministrators

• Who can access shared resources

• Which users have access to a resource, from which places users can access this resource, and during what period of time

• A password policy that prevents misuse of logins and passwords

• A proper audit policy to trace unauthorized access attempts to any resource, by tracing information about failed access

In other words: To control access to important resources in your company, you need to identify the users who access these resources, the date and time of each access, and the location from where each access is made.

SQL Server enforces security at different levels. Any data access, such as reading the unit price of a given product, forces SQL Server to check data access security, following predefined steps, according to the SQL Server security model. Figure 1.12 shows how SQL Server checks for data access security, which is in summary:

According to the preceding comments, a login gives you access to SQL Server, and a user gives you access to a specific database. You learn in more detail this important process in the following sections.

Authentication Modes

SQL Server 2000 is integrated with Windows, and it can use the authentication mode defined in your Windows network. SQL Server 2000 can collaborate with Windows NT or Windows 2000 to authenticate this user. In other cases, some users will access SQL Server from other networks, not members of any Windows domain, yet you still need to provide them secure access to SQL Server. In these cases, SQL Server is the only service responsible for user authentication.

SQL Server supports two authentication modes:

• Windows Authentication only, when only valid Windows users will have access to SQL Server.

• Mixed mode, when SQL Server accepts either Windows authentication or SQL Server authentication.

To specify the authentication mode for your server, use Enterprise Manager, open the SQL Server properties form, and select the required authentication mode in the Security tab, as you can see in Figure 1.13. After you change the authentication mode, you must stop and restart SQL Server.

Windows Integrated Authentication

Usually, you have your SQL Server 2000 installed in a member server of a Windows domain. In this case, every user needs a valid domain login and password to start a session on any computer member of this domain.

Every domain user can be a member of any number of domain groups, and every domain group can belong to one or more local groups on every specific server. In other words, one specific domain user can be authenticated in a specific server by his or her direct or indirect membership in one or more local or domain groups.

The permissions that any user has when trying to access any server resource, printer, shared folder or file, or network application, is the combination of permissions applied to every group where this particular user has membership; the only exception is no access, which cancels any possible permissions that this user has.

When a user tries to connect to SQL Server using Windows Integrated Authentication, it is not necessary to supply the login name and password again. Windows has already checked this data and SQL Server does not need to check it again. Windows informs SQL Server about the identity of this user and the windows groups'membership.

SQL Server must check whether the user has a valid login defined on SQL Server for his or her own Windows login, or for any windows group where this user has membership. If this search is successful, SQL Server checks whether any of these valid SQL Server logins has denied access to SQL Server, in which case the user connection is rejected. If none of the valid logins for this Windows user has denied access to SQL Server, the connection is established.

Windows authentication has an important advantage: You use Windows to control who can access your network and how, why not use Windows to control who can access SQL Server and how? SQL is a networked Windows application, after all.

Using this type of authentication, SQL Server doesn't store password information for Windows logins.

Mixed (SQL and Windows) Authentication

In some environments, you can have users who are authenticated by Windows and users without Windows credentials, or in other words, they don't have a Windows account. If this is the case, you should use Mixed Authentication Mode. In this case, any user can con nect to SQL Server either by Windows Authentication or by SQL Server Authentication.

Perhaps you want to have an extra security layer to access your databases and, even if Windows has authenticated the user, you want to force the user to provide a valid login and password to connect to SQL Server. In other cases, users access SQL Server from remote networks, perhaps from the Internet, and Windows cannot authenticate them.

Connecting to SQL Server: Logins

To allow users access to SQL Server, you must create a login for them. When you install a new instance of SQL Server, you have only the following logins:

• BUILTINAdministrators—This is the login associated with the local Administrator group in the local server where SQL Server is installed. Members of this group are considered SQL Server administrators by default. You can remove this login.

• sa—This is the SQL Server system administrator login account used for SQL Server authentication. This login cannot be removed, even if you select Windows Integrated Authentication only.

• YourDomainSQLService—This is the login account for the SQL Server service account, if you selected, as recommended, to use a domain account as a SQL Server service account (SQLService, in this case).

You can add more logins using Enterprise Manager or using the sp_addlogin or sp_grantlogin system-stored procedures, as in Listing 1.1.

To create new logins in SQL Server you have the following choices:

• Execute sp_addlogin to create a new login using SQL Server Authentication. In this case, you can specify the password, default language, and default database for this login.

• Execute sp_grantlogin to grant access to SQL Server to an existing local user in the server that is running SQL Server. In this case, the name of the login should have the format 'BUILTINUser' .

• Execute sp_grantlogin to grant access to SQL Server to an existing local group in the server that is running SQL Server. In this case, the name of the login should have the format 'BUILTINLocalGroup'. A typical example of this is the default 'BUILTINAdministrators' login created during setup.

• Execute sp_grantlogin to grant access to SQL Server to an existing domain user in a domain trusted by the domain in which SQL Server is running. In this case, the name of the login should have the format 'DomainNameUser'. This is the case used by the service account login 'YourDomainSQLService'.

• Execute sp_grantlogin to grant access to SQL Server to an existing domain global group in a domain trusted by the domain where SQL Server is running. In this case, the name of the login should have the format 'DomainNameGlobalGroup' .

Code Listing 1.1. Create Logins Using the sp_addlogin and sp_grantlogin System Stored Procedures

							
-- Create a SQL Server login
-- using English as a default language
-- with Northwind as a default database

EXEC sp_addlogin
@loginame = 'Tim'
, @passwd = 'TimPassword'
, @defdb = 'Northwind'
, @deflanguage = 'English'

-- Create a SQL Server login
-- using Spanish as a default language
-- without password, and without default database

EXEC sp_addlogin
@loginame = 'Pedro'
, @deflanguage =Spanish'

-- Create a SQL Server login
-- for the local Guest Windows account

EXEC sp_grantlogin 'BUILTINGuest'

-- Create a SQL Server login
-- for the domain Guest account

EXEC sp_grantlogin 'YourDomainGuest'

-- Create a SQL Server login
-- for the local group Users Windows account

EXEC sp_grantlogin 'BUILTINUsers'

-- Create a SQL Server login
-- for the domain group Domain Users account

EXEC sp_grantlogin 'YourDomainDomain Users'
 
New login created.
New login created.
Granted login access to 'BUILTINGuest'.
Granted login access to 'YourDomainGuest'.
Granted login access to 'BUILTINUsers'.
Granted login access to 'YourDomainDomain Users'.

As you see in Listing 1.1, when you add a new SQL Server login, you can specify a password, a default database, and default language for this particular login. When you add a Windows login using sp_grantlogin, you cannot specify these options. However,

• The password cannot be specified because Windows will check the password when the user tries to connect to Windows. SQL Server does not need to know this password.

• You can use the sp_defaultlanguage system stored procedure to mod ify the default language that this particular login will use to communicate with SQL Server. This setting affects custom error messages and date output formats. This procedure is valid for SQL Server and Windows logins.

• You can execute the sp_defaultdb system stored procedure to modify the default database for this login. This procedure is valid for SQL Server and Windows logins.

To deny access to SQL Server to a particular login, you can use the following:

• EXECUTE sp_denylogin 'DomainUser' denies access to SQL Server to a domain user. The login still exists, but nobody can connect to SQL Server using this login. If the login is a Windows group, none of the members of this group will be able to connect to SQL Server—regardless of the existence of other logins they might have access to.

• EXECUTE sp_revokelogin 'DomainUser' permanently removes this Windows login from SQL Server. This does not guarantee that the users or members of this Windows group will not have access to SQL Server, because they can still belong to one or more Windows groups with valid logins in SQL Server.

• EXECUTE sp_droplogin 'SQLUser' denies access to SQL Server to a SQL Server login. In this case, this login will be deleted from SQL Server permanently.

Using Databases: Users

After the user connects to SQL Server using a valid login, the connection is established, but there is not much to do. To access real data, the user needs access to a database. This is achieved by creating a user on that database.

When you set up SQL Server, the systems databases contain the following users:

• dbo—This is the database owner with full permissions, by default, on the entire database. This user cannot be removed.

• guest—This is the default user for logins that don't have a specific user in a database. Because every system database, Northwind, and Pubs databases have a guest user, any valid login can use these databases directly. This user can be removed to guarantee authenticated access to a database only.

To create a user in the current database, you can use the sp_grantdbaccess stored procedure, as in Listing 1.2. Each login can be mapped to a single user per database. Even if the login were related to a Windows group, the mapped user in the database is considered a logically individual user.

Code Listing 1.2. Use sp_grantdbaccess to Grant Logins Access to a Database

							
USE Northwind
GO

-- Create a Tim user in the Northwind database

EXEC sp_grantdbaccess
@loginame = 'Tim'

-- Create a User in Northwind
-- for the local Guest login

EXEC sp_grantdbaccess
@loginame = 'BUILTINGuest'
, @name_in_db = 'LocalGuest'

-- Create a user in Northwind
-- for the domain Guest account

EXEC sp_grantdbaccess
@loginame = 'YourDomainGuest'
, @name_in_db = 'GlobalGuest'

-- Create a user in Northwind
-- for the local group Users Windows account

EXEC sp_grantdbaccess
@loginame = 'BUILTINUsers'
, @name_in_db = 'LocalUsers'

-- Create a user in Northwind
-- for the domain group Domain Users account

EXEC sp_grantdbaccess
@loginame = 'YourDomainDomain Users'
, @name_in_db = 'GlobalUsers'

Granted database access to 'Tim'.
Granted database access to 'BUILTINGuest'.
Granted database access to 'CallSQLGuest'.
Granted database access to 'BUILTINUsers'.
Granted database access to 'CallSQLDomain Users'.

As you saw in Listing 1.2, users in a database do not have to have exactly the same names as the corresponding logins in SQL Server. However, it is recommended that users and logins have the same names to avoid maintenance problems .

Server and Database Roles

After a login has been granted access to a database, because it has a user defined on that database, it does not have access to specific data yet. The user needs specific permissions to access data from database objects.

Applying and managing to individual users can be very complex. SQL Server 2000 provides roles, at server and database levels, to simplify permissions management:

• Fixed server roles—Group logins by general server permissions to simplify general logins administration. These fixed server roles cannot be modified or dropped, not even their permissions. You can only change their membership.

• Fixed database roles—Group users in a database by functionality, reducing permissions maintenance overhead. Fixed database roles cannot be modified or dropped, not even their permissions. You can only change their membership.

• User-defined database roles—Extend the functionality of fixed database roles, providing extra flexibility on grouping users at the database level to apply specific permissions.

The most important fixed server role is sysadmin (System Administrators). Login members of the sysadmin server role are not affected by SQL Server security at all.

If you want to differentiate between users with only specific administrative privileges, you can use other fixed server roles:

• Members of the serveradmin role can configure and stop or restart SQL server.

• Members of the setupadmin role can manage linked servers and specify which stored procedures will automatically run when SQL Server starts.

• Members of the securityadmin role can manage logins, including changing passwords, and assign permissions to create databases.

• Members of the processadmin role can stop processes running in SQL Server.

• Members of the dbcreator role can manage databases.

• Members of the diskadmin role can manage disk files and backup devices.

• Members of the bulkadmin role can execute BULK INSERT statements.

You can use system stored procedures to retrieve information about server roles and to manage server roles membership, as in Listing 1.3.

Code Listing 1.3. Stored Procedures to Manage Server Roles

							
-- Get a list of the server roles

EXEC sp_helpsrvrole

-- get a list of permissions of a specific server role

EXEC sp_srvrolepermission setupadmin

-- Make Tim member of the sysadmin role

EXEC sp_addsrvrolemember
@loginame = 'Tim'
, @rolename = 'sysadmin'

-- Get a list of sysadmin role members

EXEC sp_helpsrvrolemember sysadmin

-- Remove Tim from the sysadmin role

EXEC sp_dropsrvrolemember
@loginame = 'Tim'
, @rolename = 'sysadmin'
 
 ServerRole                          Description
----------------------------------- -----------------------------------
sysadmin                            System Administrators
securityadmin                       Security Administrators
serveradmin                         Server Administrators
setupadmin                          Setup Administrators
processadmin                        Process Administrators
diskadmin                           Disk Administrators
dbcreator                           Database Creators
bulkadmin                           Bulk Insert Administrators

(8 row(s) affected)

ServerRole                          Permission
----------------------------------- -----------------------------------
setupadmin                          Add member to setupadmin
setupadmin                          Add/drop/configure linked servers
setupadmin                          Mark a stored procedure as startup


(3 row(s) affected)

'Tim'added to role 'sysadmin'.

ServerRole  MemberName               MemberSID
----------- ------------------------ -------------------------------------
sysadmin    BUILTINAdministrators   0x01020000000000052000000020020000
sysadmin    YourDomainSQLService    0x0105000000000005150000003FAD1462
 FD43461E1525AF47EF030000
sysadmin    distributor_admin        0xBAC6B1014B4F23408F6B0CEF54A0AB5E
sysadmin    sa                       0x01
sysadmin    Tim                      0x6E00C5CC4408ED47A33C5B210029109F

'Tim'dropped from role 'sysadmin'.
					

At database level, you can group users in database roles. Every database contains a fixed set of fixed database roles:

• db_owner—Database owners or users who have, by default, granted permissions to perform any action on every database object.

• db_accessadmin—Database access administrators manage users in the database.

• db_securityadmin—Database security administrators manage permissions.

• db_ddladmin—Database DDL administrators can execute any Data Definition Language statement.

• db_backupoperator—Database backup operators.

• db_datareader—Database users with permissions to read data from any table in the database.

• db_datawriter—Database users with permissions to modify data on any table in the database.

• db_denydatareader—Database users with denied permissions to read data from this database.

• db_denydatawriter—Database users with denied permissions to modify data from this database.

You can use system-stored procedures to retrieve information about fixed database roles, as in Listing 1.4.

Code Listing 1.4. Stored Procedures to Manage Fixed Database Roles

							
USE Northwind
GO

-- Get a list of the database roles

EXEC sp_helpdbfixedrole

-- Get a list of permissions of a specific database role
EXEC sp_dbfixedrolepermission db_ddladmin

-- Make Tim member of the db_owner role

EXEC sp_addrolemember
@rolename = 'db_owner'
, @membername = 'Tim'

-- Get a list of db_owner database role members

EXEC sp_helprolemember 'db_owner'

-- Remove Tim from the db_owner role

EXEC sp_droprolemember
@rolename = 'db_owner'
, @membername = 'Tim'
 
DbFixedRole           Description
--------------------- -----------------------------------
db_accessadmin        DB Access Administrators
db_backupoperator     DB Backup Operator
db_datareader         DB Data Reader
db_datawriter         DB Data Writer
db_ddladmin           DB DDL Administrators
db_denydatareader     DB Deny Data Reader
db_denydatawriter     DB Deny Data Writer
db_owner              DB Owners
db_securityadmin      DB Security Administrators

(9 row(s) affected)

DbFixedRole           Permission
--------------------- -----------------------------------
db_ddladmin           All DDL but GRANT, REVOKE, DENY
db_ddladmin           dbcc cleantable
db_ddladmin           dbcc show_statistics
db_ddladmin           dbcc showcontig
db_ddladmin           REFERENCES permission on any table
db_ddladmin           sp_changeobjectowner
db_ddladmin           sp_fulltext_column
db_ddladmin           sp_fulltext_table
db_ddladmin           sp_recompile
db_ddladmin           sp_rename
db_ddladmin           sp_tableoption
db_ddladmin           TRUNCATE TABLE
(12 row(s) affected)

'Tim'added to role 'db_owner'.

DbRole         MemberName   MemberSID
-------------- ------------ ------------------------------------
db_owner       dbo          0x01
db_owner       Tim          0x6E00C5CC4408ED47A33C5B210029109F

(2 row(s) affected)

'Tim'dropped from role 'db_owner'.

You can create your own database roles. This is useful in some specific circumstances:

• If you cannot create Windows groups to group your SQL Server users, you can create database roles to group users according to the different set of permissions they must have.

• If you have Windows users and non-Windows users, you will have Windows logins and SQL Server logins, and you want to apply permissions to them as a group, regardless of their login origin.

You can manage database roles with several stored procedures similar to the ones used in Listings 1.3 and 1.4. Listing 1.5 shows how to create a database role, how to manage membership of the role, and how to remove the database role when it is no longer necessary .

Code Listing 1.5. Stored Proceduresto Manage User-Defined Database Roles

							
USE Northwind
GO

-- Add a new role

EXEC sp_addrole 'SalesAdmin'

-- Get a list of the database roles
EXEC sp_helprole

-- Make the SqlesAdmin role member of the
-- db_datareader database role

EXEC sp_addrolemember
@rolename = 'db_datareader'
, @membername = 'SalesAdmin'

-- Make Tim member of the SalesAdmin role

EXEC sp_addrolemember
@rolename = 'SalesAdmin'
, @membername = 'Tim'

-- Get a list of db_owner database role members

EXEC sp_helprolemember 'SalesAdmin'

-- Remove Tim from the db_owner role

EXEC sp_droprolemember
@rolename = 'SalesAdmin'
, @membername = 'Tim'

-- Drop the SalesAdmin role

EXEC sp_droprole 'SalesAdmin'
New role added.

RoleName           RoleId IsAppRole
------------------ ------ -----------
public             0      0
db_owner           16384  0
db_accessadmin     16385  0
db_securityadmin   16386  0
db_ddladmin        16387  0
db_backupoperator  16389  0
db_datareader      16390  0
db_datawriter      16391  0
db_denydatareader  16392  0
db_denydatawriter  16393  0
SalesAdmin         16400  0

(11 row(s) affected)
'SalesAdmin'added to role 'db_datareader'.

'Tim'added to role 'SalesAdmin'.

DbRole         MemberName   MemberSID
-------------- ------------ ------------------------------------
db_owner       Tim          0x6E00C5CC4408ED47A33C5B210029109F

(1 row(s) affected)

'Tim'dropped from role 'SalesAdmin'.

Role dropped.

There is a special group on every SQL Server database; its name is public and every user and role in that database belongs to public. The public role is very useful to define the default permissions that every user has in the database .

Permissions

Now that you have users in a database and you grouped them by database roles, you can apply permissions to them. These permissions can be

• Statement permissions—Permissions to execute specific Transact-SQL statements, to create database objects, and to execute backups.

• Data access permissions—Permissions to read, delete, insert, update, or reference data, or permissions to execute stored procedures and user-defined functions.

Regardless of the type of permission to apply, the user permission to perform any action on a specific database object or statement can have any of these three states:

• Neutral—No permissions. In this case, the user will have permissions to perform the required action, depending on permissions applied to other roles or Windows groups the user might belong to.

• Denied—The user cannot perform the required action. It does not matter whether the user belongs to other roles or groups with permissions to perform this action. Deny overrides everything else.

• Granted—In principle, the user has permissions to proceed with this action. However, the final permissions depend on other existing permissions on the same action applied to other groups or roles this par ticular user belongs to.

To decide which permissions a particular user has, SQL Server must combine permissions from the user with permissions from the different roles and groups in which this user has membership. The only exception to this is if any permission is denied to the user or any of the user's roles or groups, the final permission will be denied .

Statement Permissions

You can define permissions for the following statements:

• BACKUP DATABASE—To execute full or differential database backups

• BACKUP LOG—To perform transaction log backups

• CREATE DATABASE—To create databases

• CREATE DEFAULT—To create independent DEFAULT objects

• CREATE FUNCTION—To create user-defined functions

• CREATE PROCEDURE—To create stored procedures

• CREATE RULE—To create independent RULE objects

• CREATE TABLE—To create tables

• CREATE VIEW—To create views

To grant statement permissions, you use the GRANT statement with the following syntax:

GRANT Statement TO Security_account

or

GRANT ALL TO Security_account

Security_account can be a database user or a user-defined database role.

To deny statement permissions, use the DENY statement with the following syntax:

DENY Statement TO Security_account

or

DENY ALL TO Security_account

To revoke previously granted or denied statement permission, use the REVOKE statement with the following syntax:

REVOKE Statement FROM Security_account

or

REVOKE ALL FROM Security_account

You can manage statement permissions in Enterprise Manager as well, in the Permissions tab available in the Database Properties form, as shown in Figure 1.14.

Data Access Permissions

You can protect access to the data using the GRANT, DENY, and REVOKE statements you saw already in the preceding section.

Depending on the object, you can manage permissions for different actions. Figure 1.15 shows the permissions that can be applied to every different object .

You can access object permissions in Enterprise Manager in three ways:

• Through the Object Permissions form, you can see the permissions applied to every user and role for a particular object. Figure 1.16 shows you the Object Permissions form.

  Figure 1.16. Data access permissions through the Object Permissions form.

  

• Through the Database User Permissions form, where for every user you can see the permissions applied on every object in the database. Figure 1.17 shows the User Permissions form.

  Figure 1.17. Data access permissions through the Database User Permissions form.

  

• Through the Database Role Permissions form, where for every user you can see the permissions applied on every object in the database, as in Figure 1.18.

  Figure 1.18. Data access permissions through the Database Role Permissions Form.

  

Some users have special data access permissions:

• Members of the sysadmin server role have full permissions on all objects and all databases available in the server they manage. Even if you specifically deny permissions to these users, they can still perform any action because SQL Server security does not affect SQL Server administrators.

• Members of the db_owner database role have, by default, full permissions on all objects from the database they own. However, it is possible to deny permissions on specific actions and objects to members of the db_owner role. In that case, DENY affects them, as with any other database user. However, members of the db_owner role can revoke previously denied permissions and take ownership of any object if necessary.

• Object owners have, by default, full permissions on the objects they own. However, the object owner, members of the sysadmin server role, or members of the db_owner database role can deny permissions on specific actions to the object owner. In that case, DENY affects the object owner, as with any other database user. However, object owners can revoke previously denied permissions as long as they are still owners of the target object.

Application Security

In the preceding sections, you learned how to manage permissions to execute specific Data Manipulation Language (DML) or Data Definition Language (DDL) statements, affecting database users and roles. Managing permissions in this way can be complex as soon as the number of database objects and users increases. Using SQL Server 2000, you can simplify permissions management using Application Security in different ways:

• Creating stored procedures to retrieve and modify data—You can give users access to the data through these stored procedures only, denying permissions to access directly the underlying tables. Chapter 8 covers in detail how to implement applications security using stored procedures.

• Provide indirect access to the data through views—You can design your views to provide access to particular columns only and to particular sets of rows, hiding the actual tables to the users. Chapter 3, "Working with Tables and Views," covers how to implement applications security using views in detail.

• Provide indirect access to the data through inline user-defined functions—Inline user-defined functions are similar to views, but they expose parameters that give your users extra flexibility. You learn in Chapter 10, "Enhancing Business Logic: User-Defined Functions (UDF)," how to implement applications security using inline user-defined functions.

• Provide indirect read-only access to the data through multistatement table-valued user-defined functions—These functions work similar to stored procedures, but they do not modify actual data. Users can work with these user-defined functions as if they were tables. You can find examples of table-valued functions in Chapter 10.

When a user invokes a stored procedure, view, or user-defined function, SQL Server checks first whether every required object belongs to the same owner. In this case, SQL server will check permissions only in the invoked object, and it does not check permissions on the underlying objects at all. However, if the ownership chain is broken on any of the referenced objects, SQL Server must check permissions on every individual object.

Chapters 3, 8, and 10 contain information and examples about implications of ownership chains on application security.

Imagine now a different situation:

Your company has a sales application, called SalesInfo, to manage sales orders. Customers call your company to place orders and you have a dedicated team of sales specialists who take orders on the phone and enter them into your database using the SalesInfo application.

You have a call center with 100 computers and you have in total about 200 sales specialists who use these computers in different shifts. Sales specialists sit every day at a different computer, and you give them an individual domain account to log in to your network. Other employees can use the SalesInfo application from their desktops or mobile computers.

Different employees might have different permissions in SQL Server, through different Windows groups and database roles. However, the SalesInfo application must execute specific actions in the database, regardless of the employee who is using the application. You want to make sure that if an employee has access to this application, he or she will be able to use it at full functionality, regardless of the individual database permissions the user might have.

To solve this problem, you could create a database role and assign permissions to this new role. However, permissions on this database role will be merged with other permissions the user might have, resulting in different actual permissions per employee.

SQL Server 2000 provides a solution to this problem through application roles. An application role is a special type of database role that has no members and whose permissions are not combined with any user's permissions.

You can create an application role using the sp_addapprole system stored procedure, providing a name for the new application role and a password for its activation. To change the application role password, you can execute the sp_setapprolepassword system stored procedure.

Applications can activate an application role using the sp_setapprole system stored procedure. User permissions are disregarded when an application role is active.

To drop an application role, you can use the sp_dropapprole system stored procedure. Listing 1.6 contains an example of how to create an application role, change its password, and activate the application role. It also shows an example of how to drop the application role.

Code Listing 1.6. Application Role Management

							
USE Northwind
GO

-- Create an Application Role

EXEC sp_addapprole
@rolename = 'SalesInfo'
,@password = 'SIPassword'
GO

-- Change the Application Role password

EXEC sp_approlepassword
@rolename = 'SalesInfo'
, @newpwd = 'NewSIPassword'
GO

-- Drop the Application Role
-- Note: execute this code alone
-- to drop the SalesInfo application role

/*
EXEC sp_dropapprole 'SalesInfo'

*/

-- Activate the Appliction role
-- with encryption

EXEC sp_setapprole
@rolename = 'SalesInfo'
, @password = { Encrypt N 'NewSIPassword'}
, @encrypt = 'ODBC'

-- Activate the Appliction role
-- without encryption
-- Note: execute this conde instead of
-- the preceding statement
-- if you do not want to use encryption

/*

EXEC sp_setapprole
@rolename = 'SalesInfo'
, @password = 'NewSIPassword'

*/

New application role added.

(1 row(s) affected)

The password for application role 'SalesInfo'has been changed.
The application role 'SalesInfo'is now active.
					

Applications can activate an application role at any time, providing the application role name and the specific application role password. The general security process for the SalesInfo application could be as follows: