SQL control constructs. Logins and fixed server roles. Connecting to SQL server
If you've ever written locking schemes in other database languages to overcome the lack of locking (as I have), you may have been left with the feeling that you have to deal with locking yourself. Let me assure you that the lock manager can be completely trusted. However, SQL Server offers several methods for managing locks, which we will discuss in detail in this section.
Don't apply locking settings or change isolation levels randomly—trust the SQL Server lock manager to balance contention and transaction integrity. Only if you are absolutely sure that the database schema is well configured and the program code is literally polished, you can slightly adjust the behavior of the lock manager to solve a specific problem. In some cases, setting select queries to not lock will solve most problems.
Setting the connection isolation level
The isolation level determines the duration of the general or exclusive connection blocking. Setting the isolation level affects all queries and all tables used for the duration of the connection or until one isolation level is explicitly replaced by another. The following example sets tighter isolation than the default and prevents non-repeated reads:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Valid isolation levels are:
Read uncommitted? serializable
Read committed? snapshot
Repeatable read
You can check your current isolation level using the Database Integrity Check (DBCC) command:
DBCC USEROPTIONS
The results will be as follows (abbreviated):
Set Option Value
isolation level repeatable read
Isolation levels can also be set at the query or table level using locking options.
Using Database Snapshot Level Isolation
There are two options for the isolation level of database snapshots: snapshot and read committed snapshot. Snapshot isolation works like a repeatable read without dealing with locking issues. Read commited snapshot isolation mimics SQL Server's default read commited level, also eliminating locking issues.
While transaction isolation is typically configured at the connection level, snapshot isolation must be configured at the database level because it
effectively tracks the versioning of rows in the database. Row versioning is a technology that creates copies of rows in the TempDB database for updating. In addition to the main loading of the TempDB database, row versioning also adds a 14-byte row identifier.
Using Snapshot Isolation
The following snippet enables the snapshot isolation level. To update the database and enable the snapshot isolation level, no other connections must be established to this database.
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
| To check whether snapshot isolation is enabled on a database, run the following SVS query: SELECT name, snapshot_isolation_state_desc FROM [ * sysdatabases.
Now the first transaction starts reading and remains open (i.e. not committed): USE Aesop
BEGIN TRAN SELECT Title FROM FABLE WHERE FablD = 2
The following result will be obtained:
At this time, the second transaction begins updating the same row that was opened by the first transaction:
SET TRANSACTION ISOLATION LEVEL Snapshot;
BEGIN TRAN UPDATE Fable
SET Title = ‘Rocking with Snapshots’
WHERE FablD = 2;
SELECT * FROM FABLE WHERE FablD = 2
The result is as follows:
Rocking with Snapshots
Isn't that surprising? The second transaction was able to update the row even though the first transaction remained open. Let's return to the first transaction and see the original data:
SELECT Title FROM FABLE WHERE FablD = 2
The result is as follows:
If you open the third and fourth transactions, they will see the same original value of The Bald Knight:
Even after the second transaction commits the changes, the first one will still see the original value, and all subsequent transactions will see the new value, Rocking with Snapshots.
Using ISOLATION Read Commited Snapshot
Read Commited Snapshot isolation is enabled using similar syntax:
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON
Similar to Snapshot isolation, this layer also uses row versioning to alleviate locking issues. If we take as a basis the example described in the previous section, then in in this case the first transaction will see the changes made by the second as soon as they are committed.
Because Read Commited is the default isolation level in SQL Server, only setting the database parameters is required.
Resolving Write Conflicts
Transactions that write data while the Snapshot isolation level is set may be blocked by previous uncommitted write transactions. Such a lock will not cause a new transaction to wait - it will simply generate an error. For processing similar situations use a try expression. . . catch, wait a couple of seconds and try the transaction again.
Using blocking options
Blocking parameters allow you to make temporary adjustments to the blocking strategy. While the isolation level affects the connection as a whole, the locking options are specific to each table in a particular query (Table 51.5). The WITH option (lock_option) is placed after the table name in the FROM clause of the query. For each table, you can specify multiple parameters, separated by commas.
|
Table 51.5. Blocking options |
Parameter blocking |
Description |
Isolation level. Does not set or hold a lock. Equivalent to no blocking |
Default transaction isolation level |
Isolation level. Holds shared and exclusive locks until the transaction is confirmed |
Isolation level. Holds a shared lock until the transaction completes |
Skip blocked rows instead of waiting |
Enable locking at the row level instead of the page, extent, or table level |
Enable page-level locking instead of table-level locking |
Automatic escalation of row, page and extent level locks to table level granularity |
Parameter blocking |
Description |
Failure to apply or maintain locks. Same as ReadUnCommitted |
Enable an exclusive table lock. Preventing other transactions from working with the table |
Hold a shared lock until the transaction commits (same as Serializable) |
Using an update lock instead of a general one and holding it. Locking other writes to data between initial reads and writes |
Holding an exclusive data lock until the transaction is confirmed |
The following example uses a locking option in the FROM clause of the UPDATE statement to prevent the manager from escalating the lock granularity:
USE OBXKites UPDATE Product
FROM Product WITH (RowLock)
SET ProductName = ProductName + ' Updated 1
If a query contains subqueries, be aware that each query's table access generates a lock, which can be controlled using parameters.
Index-level locking restrictions
Isolation levels and blocking settings are applied at the connection and request level. The only way to manage table-level locks is to limit the lock granularity based on specific indexes. Using the sp_indexoption system stored procedure, row and/or page locks can be disabled for a specific index using the following syntax: sp_indexoption 'index_name 1 .
AllowRowlocks or AllowPagelocks,
This may be useful in a number of ways special occasions. If the table frequently experiences waits due to page locks, then setting allowpagelocks to off will establish row-level locking. Reduced lock granularity will have a positive effect on competition. Additionally, if the table is rarely updated but read frequently, row- and page-level locks are not desirable; In this case, the optimal locking level is at the table level. If updates are performed infrequently, then exclusive table locking will not cause much of a problem.
The Sp_indexoption stored procedure is designed to fine-tune the data schema; that's why it uses index-level locking. To restrict locks on a table's primary key, use sp_help table_name to find the name of the primary key index.
The following command configures the ProductCategory table as an infrequently updated categorizer. The sp_help command first displays the name of the table's primary key index: sp_help ProductCategory
The result (truncated) is:
index index index
name description keys
PK_____________ ProductCategory 79A814 03 nonclustered, ProductCategorylD
unique, primary key located on PRIMARY
Given the actual name of the primary key, the system stored procedure can set the index locking parameters:
EXEC sp_indexoption
‘ProductCategory.РК__ ProductCategory_______ 7 9A814 03′,
'AllowRowlocks', FALSE EXEC sp_indexoption
‘ProductCategory.PK__ ProductCategory_______ 79A81403′,
'AllowPagelocks', FALSE
Managing lock wait times
If a transaction is waiting for a lock, then this wait will continue until the lock becomes possible. By default there is no timeout limit - theoretically it can last forever.
Luckily, you can set the lock timeout using the set lock_timeout connection option. Set this parameter to the number of milliseconds, or if you don't want to limit the time, set it to -1 (which is the default). If this parameter is set to 0, the transaction will be rejected immediately if there is any blocking. In this case, the application will be extremely fast, but ineffective.
The following request sets the lock timeout to two seconds (2000 milliseconds):
SET Lock_Timeout 2 00 0
If a transaction exceeds the configured timeout limit, error number 1222 is generated.
I strongly recommend setting a lock timeout limit at the connection level. This value is chosen based on the typical performance of the database. I prefer to set the timeout to five seconds.
Evaluating Database Contention Performance
It's very easy to create a database that doesn't address lock contention and contention issues when testing on a group of users. Real test- this is when several hundred users simultaneously update orders.
Competition testing needs to be properly organized. At one level, it must contain the simultaneous use of the same final form by multiple users. A .NET program that constantly simulates
user viewing and updating data. Good test should run 20 instances of a script that continuously loads the database and then let the testing team use the application. The number of locks will help you see the performance monitor discussed in Chapter 49.
It is better to test multiplayer competition several times during the development process. As the MCSE exam manual states, “Do not allow the test to real conditions was the first."
Application locks
SQL Server uses very complex circuit blocking. Sometimes a process or resource other than data requires a lock. For example, it may be necessary to run a procedure that causes harm if another user runs another instance of the same procedure.
Several years ago I wrote a program for cabling in nuclear power plant projects. Once the plant geometry was designed and tested, engineers entered the cable composition, location, and types of cables used. After several cables were inserted, the program formed the route for laying them so that it was as short as possible. The procedure also took into account cabling safety issues and separated incompatible cables. At the same time, if multiple routing procedures were run simultaneously, each instance would attempt to route the same cables, resulting in incorrect results. App blocking has become a great solution to this problem.
App lock opens the whole world SQL locks intended for use in applications. Instead of using data as a lockable resource, application locks lock the use of all user resources declared in the sp__GetAppLock stored procedure.
Application locking can be applied in transactions; in this case, the blocking mode can be Shared, Update, Exclusive, IntentExclusice or IntentShared. The return value from the procedure indicates whether the lock was applied successfully.
0. The lock was installed successfully.
1. The lock was acquired when another procedure released its lock.
999. The lock was not installed for another reason.
The sp_ReleaseApLock stored procedure releases the lock. The following example demonstrates how an application lock can be used in a batch or procedure: DECLARE @ShareOK INT EXEC @ShareOK = sp_GetAppLock
@Resource = 'CableWorm',
@LockMode = 'Exclusive'
IF @ShareOK< 0
...Error handling code
... Program code ...
EXEC sp_ReleaseAppLock @Resource = 'CableWorm'
When application locks are viewed using Management Studio or sp_Lock, they appear with type APP. The following listing is a shortened output of sp_Lock running at the same time as the above code: spid dbid Objld Indld Type Resource Mode Status
57 8 0 0 APP Cabllf 94cl36 X GRANT
There are two small differences to note in how application locks are handled in SQL Server:
Deadlocks are not detected automatically;
If a transaction acquires a lock several times, it must release it exactly the same number of times.
Deadlocks
Deadlock is a special situation that occurs only when transactions with multiple tasks compete for each other's resources. For example, the first transaction has acquired a lock on resource A and needs to lock resource B, and at the same time the second transaction, which has locked resource B, needs to lock resource A.
Each of these transactions waits for the other to release its lock, and neither can complete until this happens. If there is no external influence or one of the transactions ends for a certain reason (for example, due to timeout), then this situation can continue until the end of the world.
Deadlocks used to be a serious problem, but SQL Server can now resolve them successfully.
Creating a Deadlock
The easiest way to create a deadlock situation in SQL Server is to use two connections in the query editor of Management Studio (Figure 51.12). The first and second transactions attempt to update the same rows, but in the opposite order. Using the third window to run the pGetLocks procedure, you can monitor locks.
1. Create a second window in the query editor.
2. Place the Step 2 block code in the second window.
3. Place the Step 1 block code in the first window and press the key
4. In the second window, similarly execute the code Step 2.
5. Return to the first window and execute the Step 3 block code.
6. After a short period of time, SQL Server will detect the deadlock and automatically resolve it.
Below is the example code.
– Transaction 1 — Step 1 USE OBXKites BEGIN TRANSACTION UPDATE Contact
SET LastName = 'Jorgenson'
WHERE ContactCode = 401′
Puc. 51.12. Creating a deadlock situation in Management Studio using two connections (their windows are located at the top)
Now the first transaction has acquired an exclusive lock on the record with the value 101 in the ContactCode field. The second transaction will acquire an exclusive lock on the row with a value of 1001 in the ProductCode field, and then attempt to exclusively lock the record already locked by the first transaction (ContactCode=101).
– Transaction 2 — Step 2 USE OBXKites BEGIN TRANSACTION UPDATE Product SET ProductName
= 'DeadLock Repair Kit'
WHERE ProductCode = '1001'
SET FirstName = 'Neals'
WHERE ContactCode = '101'
COMMIT TRANSACTION
There is no deadlock yet because transaction 2 is waiting for transaction 1 to complete, but transaction 1 is not yet waiting for transaction 2 to complete. In this situation, if transaction 1 completes its work and executes the COMMIT TRANSACTION statement, the data resource will be released and transaction 2 will be safely will be able to block the block she needs and continue her actions.
The problem occurs when transaction 1 tries to update a row with ProductCode=l. However, it will not receive the exclusive lock necessary for this, since this record is locked by transaction 2:
– Transaction 1 – Step 3 UPDATE Product SET ProductName
= 'DeadLock Identification Tester'
WHERE ProductCode = '1001'
COMMIT TRANSACTION
Transaction 1 will return the following error text message after a couple of seconds. The resulting deadlock can also be seen in SQL Server Profiler (Figure 51.13):
Server: Msg 1205, Level 13,
State 50, Line 1 Transaction (Process ID 51) was
deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Transaction 2 will complete its work as if the problem never existed:
(1 row(s) affected)
(1 row(s) affected)
Rice. 51.13. SQL Server Profiler allows you to monitor deadlocks using the Locks:Deadlock Graph event and identify the resource causing the deadlock
Automatic deadlock detection
As demonstrated in the code above, SQL Server automatically detects a deadlock situation by checking for blocking processes and rolling back transactions.
who completed the least amount of work. SQL Server constantly checks for the existence of cross-locks. Deadlock detection latency can vary from zero to two seconds (in practice, the longest I've had to wait for this is five seconds).
Handling Deadlocks
When a deadlock occurs, the connection selected as the deadlock victim must retry its transaction. Since the work must be redone, it is good that the transaction that managed to complete the least amount of work is rolled back - it is the one that will be repeated from the beginning.
Error code 12 05 should be intercepted by the client application, which should restart the transaction. If everything happens as expected, the user will not even suspect that a deadlock has occurred.
Instead of allowing the server itself to decide which transaction to choose as a “victim,” the transaction itself can be “played as a giveaway.” The following code, when placed in a transaction, informs SQL Server that if a deadlock occurs, the transaction should be rolled back:
SET DEADLOCKJPRIORITY LOW
Minimizing deadlocks
Even though deadlocks are easy to identify and handle, it is still best to avoid them. The following recommendations will help you avoid deadlocks.
Try to keep transactions short and free of unnecessary code. If some code does not need to be present in a transaction, it must be inferred from it.
Never make a transaction code dependent on user input.
Try to create packages and procedures that acquire locks in the same order. For example, table A is processed first, then tables B, C, etc. Thus, one procedure will wait for the second and deadlocks cannot occur by definition.
Design the physical layout to store concurrently sampled data as closely as possible on data pages. To achieve this, use normalization and choose clustered indexes wisely. Reducing the spread of blockages will help avoid their escalation. Small blocks will help avoid their competition.
Do not increase the insulation level unless necessary. A stricter level of isolation increases the duration of lockdowns.
The SQL Server environment provides a number of different control constructs, without which it is impossible to write effective algorithms.
Grouping two or more commands into a single block is done using the BEGIN and END keywords:
<блок_операторов>::=
Grouped commands are treated as a single command by the SQL interpreter. A similar grouping is required for constructions of polyvariant branching, conditional and cyclic constructions. BEGIN...END blocks can be nested.
Some SQL commands should not be run together with other commands (we are talking about the commands Reserve copy, changes in the structure of tables, stored procedures and the like), therefore their joint inclusion in the BEGIN...END construct is not allowed.
Often a certain part of a program must be executed only if some logical condition is satisfied. The syntax of the conditional statement is shown below:
<условный_оператор>::=
IF log_expression
( sql_statement | statement_block )
(sql_statement | statement_block) ]
Loops are organized using the following construction:
<оператор_цикла>::=
WHILE log_expression
( sql_statement | statement_block )
( sql_statement | statement_block )
The loop can be forcibly stopped by executing the BREAK command in its body. If you need to start the loop again without waiting for all commands in the body to be executed, you must execute the CONTINUE command.
To replace multiple single or nested conditional statements, use the following construct:
<оператор_поливариантных_ветвлений>::=
CASE input_value
WHEN (compare_value |
log_expression ) THEN
output_expression [,...n]
[ ELSE otherwise_out_expression ]
If the input value and the comparison value are the same, then the construct returns the output value. If the value of the input parameter is not found in any of the WHEN...THEN lines, then the value specified after keyword ELSE.
Basic objects of the SQL server database structure
Let's look at the logical structure of the database.
The logical structure defines the structure of the tables, the relationships between them, the list of users, stored procedures, rules, defaults, and other database objects.
Logically, data in SQL Server is organized into objects. The main SQL Server database objects include the following objects.
A brief overview of the main database objects.
Tables
All data in SQL is contained in objects called tables. Tables represent a collection of any information about objects, phenomena, processes of the real world. No other objects store data, but they can access the data in the table. Tables in SQL have the same structure as tables in all other DBMSs and contain:
· lines; each line (or record) represents a set of attributes (properties) of a specific instance of an object;
· columns; each column (field) represents an attribute or collection of attributes. A row field is the minimum element of a table. Each column in a table has a specific name, data type, and size.
Representation
Views are virtual tables whose contents are determined by a query. Like real tables, views contain named columns and rows of data. For end users a view looks like a table, but it doesn't actually contain data, but rather represents data located in one or more tables. The information that the user sees through the view is not stored in the database as a separate object.
Stored procedures
Stored procedures are a group of SQL commands combined into one module. This group of commands is compiled and executed as a single unit.
Triggers
Triggers are a special class of stored procedures that are automatically launched when data is added, changed, or deleted from a table.
Functions
Functions in programming languages are constructs that contain frequently executed code. The function performs some actions with the data and returns some value.
Indexes
An index is a structure associated with a table or view and designed to speed up the search for information in them. An index is defined on one or more columns, called indexed columns. It contains the sorted values of an indexed column or columns with references to the corresponding row in the source table or view. Improved productivity is achieved by sorting data. Using indexes can significantly improve search performance, but storing indexes requires additional space in the database.
©2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Page creation date: 2016-08-08
Posted by Mike Weiner
Co-author: Burzin Patel
Editors: Lubor Kollar, Kevin Cox, Bill Emmert, Greg Husemeier, Paul Burpo, Joseph Sack, Denny Lee , Sanjay Mishra, Lindsey Allen, Mark Souza
Microsoft SQL Server 2008 contains a number of improvements and new functionality that extend the functionality of previous versions. Administering and maintaining databases, maintaining manageability, availability, security, and performance are all the responsibilities of a database administrator. This article describes the ten most useful new features in SQL Server 2008 (in alphabetical order) that make your job as a DBA easier. In addition to a brief description, each function contains possible situations of its use and important recommendations for use.
Activity Monitor
When troubleshooting performance issues or monitoring a server in real time, an administrator will typically run a series of scripts or check relevant information sources to gather overall data about the processes running and identify the cause of the problem. SQL Server 2008 Activity Monitor brings this information together to provide at-a-glance information about running and recently running processes. The database administrator can both view high-level information and analyze any of the processes in more detail and view wait statistics, which makes it easier to identify and resolve problems.
To open Activity Monitor, right-click the registered server name in Object Explorer, then select Activity Monitor or use the standard toolbar icon in SQL Server Management Studio. Activity Monitor offers the administrator an overview section similar in appearance to Manager Windows tasks, as well as components for detailed viewing of individual processes, resource waiting, data file I/O, and recent resource-intensive requests, as shown in Fig. 1.
Rice. 1:Activity Monitor ViewSQL Server2008 in WednesdayManagement Studio
Note. Activity Monitor uses a data refresh frequency setting that can be changed by right-clicking. When choosing frequent updates data (every less than 10 seconds), the performance of a heavily loaded working system may decrease.
Using Activity Monitor, an administrator can also perform the following tasks:
· Pause and resume activity monitor with one right-click. This allows the administrator to "save" state information to a specific point in time and will not be updated or overwritten. But do not forget that when updating data manually, expanding or collapsing a section, the old data will be updated and lost.
· Right-click a row element to display full text query or graphical execution plan using the menu item "Last resource-intensive queries".
· Trace with Profiler or terminate processes in the Processes view. Profiler application events include events RPC:Completed, SQL:BatchStarting And SQL:BatchCompleted, and AuditLogin And AuditLogout.
Activity Monitor also allows you to monitor the activity of any local or remote instance of SQL Server 2005 that is registered with SQL Server Management Studio.
Audit
The ability to monitor and record events that occur, including who is accessing objects and the timing and content of changes, helps an administrator ensure compliance with regulatory or organizational security compliance standards. In addition, understanding the events occurring in the environment can also help in developing a plan to reduce risks and maintain a safe environment.
In SQL Server 2008 (only Enterprise editions and Developer) SQL Server Audit provides automation that allows administrators and other users to prepare, save, and view audits of various server and database components. The function provides the ability to audit with detail at the server or database level.
There are server-level audit action groups, such as the following:
· FAILED_LOGIN_GROUP tracks failed login attempts.
· BACKUP_RESTORE_GROUP reports when the database was backed up or restored.
· DATABASE_CHANGE_GROUP audits the time a database was created, modified, or deleted.
Database-level audit action groups include the following:
· DATABASE_OBJECT_ACCESS_GROUP is called whenever a CREATE, ALTER, or DROP statement is executed on a database object.
· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP is called when GRANT, REVOKE, or DENY statements are used on database objects.
There are other audit actions such as SELECT, DELETE and EXECUTE. For more information, including a complete list of all audit groups and actions, see SQL Server Audit Action Groups and Actions.
Audit results can be sent to a file or event log (System Log or Windows Security Event Log) for later review. Audit data is created using Extended Events- another new feature in SQL Server 2008.
SQL Server 2008 audits allow an administrator to answer questions that were previously very difficult to answer after the fact, such as, “Who dropped this index?”, “When was the stored procedure modified?”, “What change was made that might prevent a user from accessing this table?” ? and even "Who executed the SELECT or UPDATE statement on the table [ dbo.Payroll] ?».
For more information about using SQL Server auditing and examples of how to implement it, see the SQL Server 2008 Compliance Guide.
Backup compression
Database administrators for a long time suggested enabling this feature in SQL Server. Now it's done, and just in time! Recently, for a number of reasons, for example, due to the increased duration of data storage and the need to physically store more data, database sizes have begun to grow exponentially. When backing up a large database, you must allocate a significant amount of disk space for the backup files, as well as allocate a significant amount of time for the operation.
When you use SQL Server 2008 backup compression, the backup file is compressed as it is written, which not only requires less disk space, but also reduces I/O operations and takes less time to complete the backup. In lab tests with real user data, a 70-85% reduction in backup file size was observed in many cases. In addition, tests showed that the duration of copy and restore operations was reduced by approximately 45%. It should be noted that additional processing during compression increases the processor load. To separate the time-intensive copying process from other processes and minimize its impact on their operation, you can use another function described in this document - Resource Governor.
Compression is enabled by adding the WITH COMPRESSION clause to the BACKUP command (see SQL Server Books Online for more information) or by setting this option on page Options dialog box Database backup. To avoid the need to make changes to all existing backup scripts, a global setting has been implemented that enables compression of all backups created on a server instance by default. (This option is available on the page Database Settings dialog box Server properties; it can also be installed by executing the stored procedure sp_ configure with parameter value backupcompressiondefault, equal to 1). The backup command requires an explicit compression option, and the restore command automatically recognizes the compressed backup and decompresses it when restoring.
Backup compression is an extremely useful feature that saves disk space and time. For more information about configuring backup compression, see the technical note Tuning backup compression performance inSQL Server 2008 . Note. Compressed backups are supported only in SQL Server 2008 Enterprise and Developer editions, but all editions of SQL Server 2008 allow you to restore compressed backups.
Servers centralized management
Often a DBA manages many instances of SQL Server at once. The ability to centralize management and administration of many SQL instances into a single location saves significant effort and time. The centralized management server implementation, available in SQL Server Management Studio through the Registered Servers component, allows an administrator to perform various administrative operations on many SQL Servers from a single management console.
Centralized management servers allow the administrator to register a group of servers and execute on them as a single group, for example, following operations:
· Multi-server query execution: You can now run a script on multiple SQL Servers from a single source, and the data will be returned to that source, without having to log into each server separately. This can be especially useful when you need to view or compare data from multiple SQL Servers without running a distributed query. Additionally, as long as previous versions of SQL Server support the query syntax, a query run from the SQL Server 2008 Query Editor can also run on instances of SQL Server 2005 and SQL Server 2000. For more information, see the SQL Server Manageability Working Group blog at Running Multi-Server queries in SQL Server 2008 environment.
· Import and define policies on many servers: within functionality Policy-based management(another new feature in SQL Server 2008, also described in this article), SQL Server 2008 provides the ability to import policy files into individual groups of central management servers and allows you to define policies on all servers registered in a particular group.
· Manage services and access SQL Server Configuration Manager: The Servers from Central Management tool helps you create a control center where the DBA can view and even change (with the appropriate permissions) the state of services.
· Import and export of registered servers: Servers registered with Central Management Servers can be exported and imported when transferred between administrators or different installations of SQL Server Management Studio. This feature provides an alternative to an administrator importing or exporting his or her own local groups in SQL Server Management Studio.
Keep in mind that permissions are enforced using Windows Authentication, so user rights and permissions may vary across different servers that are registered in the Central Management Server group. For more information, see Administering Multiple Servers with Central Management Servers and Kimberly Tripp's blog: Central SQL Management ServersServer2008 - are you familiar with them?
Data Collector and Management Data Store
Performance tuning and diagnostics are time-consuming and may require advanced SQL Server skills as well as an understanding of the internal structure of databases. Windows Performance Monitor (Perfmon), SQL Server Profiler, and dynamic management views solved some of these problems, but they often had an impact on the server, were labor-intensive to use, or involved methods for collecting disparate data that made it difficult to combine and interpret.
To provide clear, actionable information about system performance, SQL Server 2008 provides a fully extensible performance data collection and storage tool, the Data Collector. It contains several out-of-the-box data collection agents, a centralized repository of performance data called a management data warehouse, and several pre-built reports to present the collected data. Data Collector is a scalable tool that collects and aggregates data from multiple sources, such as Dynamic Management Views, Perfmon Performance Monitor, and Transact-SQL queries, at a fully configurable data collection frequency. The data collector can be extended to collect data on any measurable application attribute.
Another useful feature of the management data warehouse is the ability to install it on any SQL Server and then collect data from one or more SQL Server instances. This minimizes the impact on the performance of production systems, and also improves scalability in the context of monitoring and collecting data from many servers. In lab testing, the observed throughput loss when running agents and running the management data warehouse on a busy server (using an OLTP workload) was approximately 4%. The performance loss can vary depending on the frequency of data collection (the test mentioned was conducted under an extended workload, with data transferred to storage every 15 minutes), and it can also increase sharply during data collection periods. In any case, you should expect some reduction in available resources because the DCExec.exe process uses a certain amount of memory and CPU resources, and writing to the management data store will increase the load on the I/O subsystem and require space allocation in the location of the data and log files. In the diagram (Figure 2) shows a typical data collector report.
Rice. 2:Data Collector Report ViewSQL Server 2008
The report shows SQL Server activity during the data collection period. It collects and displays events such as waits, CPU, I/O and memory usage, as well as statistics on resource-intensive queries. The administrator can also go to detailed consideration reporting elements, focusing on a single query or operation to investigate, identify, and resolve performance issues. These data collection, storage, and reporting capabilities enable proactive monitoring of the health of the SQLServers in your environment. When needed, they allow you to look back at historical data to understand and evaluate changes that impacted performance over the period being tracked. The Data Collector and Management Data Store are supported in all editions of SQLServer 2008 except SQLServerExpress.
Data compression
Ease of database management makes routine administration tasks much easier. As tables, indexes, and files grow in size and very large databases (VLDBs) become more common, managing data and working with large files becomes increasingly complex. In addition, the increasing demand for memory and physical I/O bandwidth that increases with the volume of data requested also increases administrative complexity and costs the organization. As a result, in many cases, administrators and organizations must either expand the memory or I/O bandwidth of their servers, or accept reduced performance.
Data compression, introduced in SQL Server 2008, helps resolve these issues. This feature allows the administrator to selectively compress any table, table partition, or index, thereby reducing disk space, memory footprint, and I/O operations. Compressing and decompressing data loads the processor; however, in many cases the additional CPU load is more than offset by the I/O gains. In configurations where I/O is a bottleneck, data compression can also provide performance gains.
In some lab tests, enabling data compression resulted in 50-80% disk space savings. Space savings varied widely: if the data contained few duplicate values, or the values used all the bytes allocated for the specified data type, the savings were minimal. However, the performance of many workloads did not increase. However, when working with data containing a lot of numeric data and a lot of repeating values, significant disk space savings and performance gains ranging from a few percent to 40-60% were observed for some sample query workloads.
SQLServer 2008 supports two types of compression: row compression, which compresses individual table columns, and page compression, which compresses pages of data using row, prefix, and dictionary compression. The level of compression achieved varies greatly depending on the data types and the contents of the database. In general, using row compression reduces the overhead on application operations, but also reduces the compression ratio, meaning less space is saved. At the same time, page compression results in more application overhead and CPU usage, but it also saves significantly more space. Page compression is a superset of row compression, that is, if an object or section of an object is compressed using page compression, row compression is also applied to it. Additionally, SQLServer 2008 supports the storage format vardecimal from SQL Server 2005 SP2. Please note that since this format is a subset of string compression, it is deprecated and will be removed from future versions of the product.
Both row compression and page compression can be enabled on a table or index online without impacting data availability to applications. At the same time, it is impossible to compress or decompress a separate section of a partitioned table in online mode without disabling it. Tests have shown that the best approach is a combined approach, in which only a few are compressed. largest tables: This achieves an excellent ratio of disk space savings (significant) to performance loss (minimal). Because a compact operation, like index creation or rebuild operations, also has requirements for available disk space, compression should be carried out taking into account these requirements. A minimum of free space will be required during the compression process if the compression starts with the smallest objects.
You can compress data using Transact-SQL statements or the Data Compression Wizard. To determine how an object might change in size when it shrinks, you can use the system stored procedure sp_estimate_data_compression_savings or data compression wizard. Database compression is only supported in SQLServer 2008 Enterprise and Developer editions. It is implemented exclusively in the databases themselves and does not require any changes to the applications.
For more information about using compression, see Creating Compressed Tables and Indexes.
Policy-based management
In many business scenarios, it is necessary to maintain specific configurations or enforce policies, either on a specific SQLServer or multiple times across a group of SQLServers. An administrator or organization may require a specific naming scheme to be applied to all user tables or stored procedures that are created, or may require certain configuration changes to be applied to multiple servers.
Policy-based management (PBM) provides the administrator with a broad range of control over the environment. Policies can be created and checked for compliance. If a scan target (such as a database engine, database, table, or SQLServer index) does not meet the requirements, the administrator can automatically reconfigure it to meet those requirements. There are also a number of policy modes (many of which are automated) that make it easy to verify policy compliance, log policy violations and send notifications, and even roll back changes to ensure policy compliance. For more information about definition modes and how they relate to aspects (the concept of policy-based management (PBM), also discussed in this blog), see the SQL Server Policy-Based Management Blog.
Policies can be exported and imported as XML files to be defined and applied across multiple server instances. Additionally, in SQLServerManagement Studio and the Registered Servers view, you can define policies across multiple servers that are registered in a local server group or a central management server group.
Previous versions of SQL Server may not provide all policy-based management functionality. However, the function making report policies can be used on SQL Server 2005 and SQL Server 2000 servers. For more information about using policy-based management, see Administering Servers Using Policy-Based Management in SQLServer Books Online. For more information about the policy technology itself, with examples, see the SQL Server 2008 Compliance Guide.
Predicted performance and concurrency
Many administrators face significant challenges supporting SQLServers with constantly changing workloads and ensuring predictable levels of performance (or minimizing variations in query plans and performance). Unexpected query performance changes, changes in query plans, and/or general performance issues can be caused by a number of reasons, including increased load from applications running on SQLServer or an upgrade to the version of the database itself. Predictability of queries and operations running on SQLServer makes it much easier to achieve and maintain availability, performance, and/or business continuity goals (meeting service level agreements and operational support level agreements).
In SQLServer 2008, several features have been changed to improve predictable performance. Thus, certain changes have been made to the plan structures of SQLServer 2005 ( consolidation of plans) and added the ability to manage lock escalation at the table level. Both improvements promote more predictable and streamlined interactions between the application and the database.
First, plan structures (Plan Guide):
SQL Server 2005 introduced improvements to query stability and predictability through a new feature at the time called “plan guides,” which provided instructions for running queries that could not be changed directly in the application. For more information, see the whitepaper Enforcing Query Plans. Although the USE PLAN query hint is a very powerful feature, it only supported DML SELECT operations and was often awkward to use due to the sensitivity of plan structures to formatting.
SQL Server 2008 expands the plan guide engine in two ways: first, it expands support for the USE PLAN query hint, which is now compatible with all DML statements (INSERT, UPDATE, DELETE, MERGE); secondly, a new function has been introduced consolidation of plans, which allows you to directly create a plan outline (pinning) of any query plan that exists in the SQL Server plan cache, as shown in the following example.
sp_create_plan_guide_from_handle
@name = N'MyQueryPlan',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
A plan guide created in any way has a database area; it is stored in a table sys.plan_guides. Plan structures only affect the query plan selection process of the optimizer, but do not eliminate the need to compile the query. Also added function sys.fn_validate_plan_guide, to review existing SQL Server 2005 plan structures and ensure they are compatible with SQL Server 2008. Plan pinning is available in SQL Server 2008 Standard, Enterprise, and Developer editions.
Secondly, escalation of blocking:
Blocking escalation often caused blocking problems and sometimes even deadlocks. The administrator had to fix these problems. In previous versions of SQLServer, it was possible to control lock escalation (trace flags 1211 and 1224), but this was only possible at instance-level granularity. For some applications this fixed the problem, but for others it caused even more problems. Another flaw in SQL Server 2005's lock escalation algorithm was that locks on partitioned tables were escalated directly to the table level rather than to the partition level.
SQLServer 2008 offers a solution to both problems. It implements new parameter, which allows you to control lock escalation at the table level. Using the ALTERTABLE command, you can choose to disable escalation or escalate to the partition level for partitioned tables. Both of these features improve scalability and performance without unwanted side effects affecting other objects in the instance. Lock escalation is set at the database object level and does not require any application changes. It is supported in all editions of SQLServer 2008.
Resource Governor
Maintaining sustainable service levels by preventing runaway requests and ensuring resources are allocated to critical workloads has previously been challenging. There was no way to guarantee the allocation of a certain amount of resources to a set of requests, and there was no control over access priorities. All requests had equal rights to access all available resources.
A new feature in SQL Server 2008, Resource Governor, helps address this issue by providing the ability to differentiate workloads and allocate resources based on user needs. Resource Governor limits are easily reconfigured in real time with minimal impact to running workloads. The distribution of workloads across the resource pool is configured at the connection level, and the process is completely transparent to applications.
The diagram below shows the resource allocation process. In this scenario, three workload pools are configured (Admin, OLTP, and Report workloads) and then the OLTP workload pool is given the highest priority. At the same time, two resource pools are configured (Pool pool and Application pool) with specified limits on memory size and processor (CPU) time. The final step is to assign the Admin workload to the Admin pool, and assign the OLTP and Report workloads to the Application pool.
The following are features to consider when using the resource governor.
- Resource Governor uses the login credential, hostname, or application name as the "resource pool identifier", so using a single login per application for certain numbers of clients per server can make pooling more difficult.
- Object grouping at the database level, where access to resources is controlled based on the database objects being accessed, is not supported.
— You can only configure the use of processor and memory resources. I/O resource management is not implemented.
- Dynamic switching of workloads between resource pools after connection is not possible.
- Resource Governor is only supported in SQL Server 2008 Enterprise and Developer editions and can only be used for the SQL Server Database Engine; management of SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) is not supported.
Transparent Data Encryption (TDE)
Many organizations pay great attention to security issues. There are many different layers that protect one of an organization's most valuable assets—its data. More often than not, organizations successfully protect the data in use through physical security measures, firewalls, and strict access restriction policies. However, if the physical storage media, such as a disk or tape with a backup copy, is lost, all of the listed security measures are useless, since an attacker can simply restore the database and obtain full access to the data. SQL
Server 2008 offers a solution to this problem through Transparent Data Encryption (TDE). With TDE encryption, data in I/O operations is encrypted and decrypted in real time; Data and log files are encrypted using a Database Encryption Key (DEK). A DEK is a symmetric key protected by a certificate stored in the server's >master database, or an asymmetric key protected by an Extensible Key Management Module (EKM).
The TDE feature protects data at rest, so data in MDF, NDF, and LDF files cannot be viewed using a hex editor or any other means. However, active data, such as the results of a SELECT statement in SQL Server Management Studio, will remain visible to users who have permission to view the table. Additionally, because TDE is implemented at the database level, the database can use indexes and keys to optimize queries. TDE should not be confused with column-level encryption - it is a separate feature that allows you to encrypt even active data.
Database encryption is a one-time process that can be started with the Transact - SQL command or from SQL Server Management Studio and then runs on a background thread. Encryption or decryption status can be monitored using the dynamic management view sys.dm_database_encryption_keys. In laboratory tests, encrypting a 100 GB database using the AES _128 encryption algorithm took about an hour. Although the overhead of using TDE is determined primarily by the application workload, in some of the tests conducted this overhead was less than 5%. One thing to be aware of that may impact performance is that if TDE is used on any of the databases on the instance, then the system database is also encrypted tempDB. Finally, when using different functions at the same time, the following must be considered:
- When you use backup compression to compress an encrypted database, the size of the compressed backup will be larger than without using encryption because encrypted data is poorly compressed.
- Database encryption does not affect data compression (row or page).
TDE enables an organization to ensure compliance with regulatory standards and overall data protection levels. TDE is supported only in SQL Server 2008 Enterprise and Developer editions; its activation does not require any changes to existing applications. For more information, see Data Encryption in SQL Server 2008 Enterprise Edition or the discussion in Transparent data encryption.
To sum it up, SQL Server 2008 offers features, enhancements, and capabilities that make your job as a database administrator easier. The 10 most popular ones are described here, but SQL Server 2008 has many other features that make life easier for administrators and other users. For lists of "Top 10 Features" for other SQL Server verticals, see the other "Top 10... in SQL Server 2008" articles on this site. For a complete list of features and their detailed descriptions, see SQL Server Books Online and the SQL Server 2008 overview website.
Rick Dobson, Ph.D. Selective translation by Ledenev S. A.
This article talks about how to manage SQL server security using Access in conjunction with SQL library Distributed Management Objects (SQL-DMO) in Visual Basic for Applications (VBA)
Introduction
This article is mainly aimed at developers of ADP projects. You may be familiar with 2 things regarding SQL server security. First, security is not an option like it was in Jet. Secondly, the security model of SQL Server is different from the security model of Access. Even if you have a rough understanding of SQL Server security, this article will teach you how to configure it to protect your project's resources.
This article may also be useful for SQL server administrators. The solutions offered here can help them manage server security using Access. This article can help administrators learn how to manage server security without using Enterprise Manager's graphical tools or running Transact SQL (T-SQL) scripts from Query Analyzer. By learning how to manage server security using Access, administrators have the option of creating a faster solution.
The article can be roughly divided into two parts about the security of SQL Server 7.0 and SQL Server 2000. First, the concept of SQL Server security is discussed, focusing on how to manage security using Access. This information is sufficient for developing multi-user applications with different groups users who have clearly defined rights to use server objects. The second part of the article demonstrates software solutions, based on the management of SQL-DMO objects (SQL Distributed Management Objects) using Microsoft Visual Basic® for Applications (VBA). Because SQL-DMO has a hierarchical object construction model, very similar to the object construction model Microsoft Office, then all that is needed is to learn more about SQL-DMO objects, properties, methods, and events regarding security. In addition, Access 2002 programmers cannot do without programming the security configuration functions in VBA, since they are not available from the Database Security menu.
SQL Server Security Concept
This section consists of 4 main parts. The introduction begins with a description of authentication - the process of checking a user for the right to access the server database. Using Enterprise Manager, SQL Server provides two types of authentication. The first part of the section shows the differences and similarities between them.
Next, we talk about the concept of an authorized user who serves to connect to the server. Authorized users are called logins. Two types of logins correspond to two SQL server authentication models. Logins are responsible for access rights to the server.
The third part introduces the concept of users. SQL Server reserves the concept of users as a concept that characterizes database users. Typically, there are several logins on a server, each login is associated with several users - one for each database that the login is allowed to access. Logins and users can belong to fixed roles that describe a set of permissions. You can control the behavior of logins and users by managing their membership in fixed server roles and fixed database roles, respectively.
Part 4 talks about user-defined database roles.
Authentication
Authentication is described as the process of authorizing a user on an instance of SQL server and some database associated with the user. Authorization is associated with a login, which describes the user's rights on the server. Authorization works like a key to a castle. If the key fits the lock, the person uses it to gain access to everything in the room whose door was locked with that lock. An SQL server usually has several locks with different keys to them. Each key gives access to a different set of databases located on the server.
SQL Server supports two types of authentication: SQL Server and Microsoft Windows® (or Windows NT® with SQL Server 7.0). These two types determine who performs login verification: SQL server or Windows. With Windows authentication, users have trusted Accounts to access SQL server. These accounts are verified by Windows, but the SQL server "knows" the account name. Windows authentication allows users to log in to Windows and the SQL server by entering only one password. With SQL Server authentication, the SQL server itself performs the authorization. With any type of authentication, the SQL server must know the authorized logins.
There are pros and cons to both the first and second types, but Windows authentication is preferable. Windows has a more complex authorization procedure, and it frees the SQL server administrator from having to manage accounts. On the other hand, not all operating systems that support SQL server support Windows authentication. For example, Microsoft Data Engine (MSDE) installed with Microsoft Office 2000 and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) installed with Office XP: both can run on Windows 98, which does not support Windows authentication. Or SQL Server 7.0 and MSDE running on Windows 95. Some organizations may prefer the ability to control access to database resources at the DBA level rather than at the more global Windows Administrator level.
Any SQL Server instance can have any of two authentication types that can be set in Enterprise Manager (I'll cover the third type later in this article). The SQL Server documentation defines the term mixed mode connection to a SQL Server, which supports both Windows and SQL Server authentication. If the server only supports Windows authentication, then the server has a Windows (or Windows NT) authentication type. By default, MSDE is installed with a mixed authentication type. MSDE 2000, on the other hand, installs with Windows authentication by default. Developers and administrators using Enterprise Manager can change the authentication type using graphical tools. Also, any application can use the SQL-DMO library to change the server authentication type.
Logins and fixed server roles
The ADP project contains a Database window similar to traditional Jet solutions. However, the ADP project connects to the server through an OLE DB connection, unlike a Jet database. In an ADP project, the login type is specified in the Data Link Properties dialog box. To call this dialog: from the database window, select the File menu, select Connection from the menu.
The Connection Options dialog box allows you to select the database server, login, and database to which the project will connect via OLE DB. There are two ways to connect to the server. The first one selects the use internal audit Windows NT security (use Windows NT integrated security). This is called Windows authentication. By selecting this option, you do not need to provide a login and password. This is because Windows authorizes the user when Windows starts. When the option to use internal security is selected, the ADP project sends the Windows login to the SQL server when attempting to create an OLE DB connection to it. By selecting the second option (use a specific user name and password), you must specify the SQL server login. In the user name field, the standard SQL server login name must be entered - the login supported by this SQL server. A password is optional, but it is strongly recommended to use a password, since together with the login, the password provides an additional level of security.
Although entering the login is key point When creating an OLE DB connection in an ADP project, the login in itself is not sufficient to grant rights to perform any tasks on the server or with the database. There is the concept of login membership in fixed server roles, which allows the ADP project to perform some server functions, such as creating new databases and managing logins. Depending on the version of the SQL server to which the ADP project connects, there are seven or eight fixed server roles. SQL Server 7.0 and MSDE provide seven fixed server roles, while SQL Server 2000 and MSDE 2000 provide eight. SQL Server Books Online (BOL) contains comprehensive documentation about fixed server roles, including T-SQL expressions for assigning and removing logins from role membership. For example, the Roles section of BOL talks about fixed server fields and a corresponding set of permissions for accessing databases, such as viewing and writing to tables.
The following table contains short review names of fixed server roles and their brief descriptions. You can run the sp_helpsrvrole system stored procedure to obtain a list of fixed server role names. Executing the system stored procedure sp_srvrolepermission prints detailed list features for each fixed server role. There are differences between the fixed server roles in 7.0 and 2000 versions of SQL Server. For example, bulkadmin is new role in SQL Server 2000. Additionally, the DROP DATABASE statement was only available to the sysadmin role in SQL Server 7.0, and SQL Server 2000 allows members of the dbcreator role to also perform this procedure.
|
Fixed server role name |
Description of the fixed server role |
| sysadmin | Execute any server or database expression |
| serveradmin | Server administration, configuration, start, stop. |
| setupadmin | Administration of linked servers and the right to run stored procedures at the server startup stage. |
| securityadmin | Management of logins and passwords. May give the right to create new databases. |
| processadmin | Executing the KILL command. |
| dbcreator | Creating, changing, renaming and deleting databases. |
| diskadmin | Managing files on disk. |
| bulkadmin | Executing BULK INSERT statements. |
The sa login is a special SQL server login. This login is a member of the sysadmin group and provides the right to perform any functions on the server. SQL Server creates this login during installation and cannot be deleted. Immediately after installation is complete, the sa login does not have a password. It is necessary to set a password for sa to ensure the security of your database server, especially for servers with mixed authentication types. Remember: servers with Windows authentication do not accept or process SQL server logins.
When installing SQL server on Windows 98 or Windows ME, the server is always installed with a mixed authentication type, so it can accept SQL server logins. The default authentication types differ for SQL Server 7.0 and MSDE from SQL Server 2000 and MSDE 2000 installed on Windows 2000 and Windows NT. For SQL Server 7.0 and MSDE, the default installation process installs the server with a mixed authentication type. In contrast, SQL Server 2000 and MSDE 2000 are installed with Windows authentication by default. Additionally, the Version 2000 installation process assigns group members Windows administrators members of the sysadmin fixed server role. Therefore, these logins are similar to the sa login, which has full control over the server.
Users and fixed database roles
Typically, users access SQL Server to work with one or more databases. For this purpose, SQL Server has a mechanism for matching logins with databases. Just as a SQL server can have one or more logins, each login can have one or more accounts associated with it. However, one account corresponds to only one login (with two exceptions). The SQL Server documentation defines accounts as database users (or users). Every database user has access to this database.
SQL Server can grant database users ownership of the database and permissions to use database objects. SQL Server provides permission assignment within the database at two independent levels. At the first level, you can assign one or more permission clusters to a user. The SQL Server documentation defines these clusters as fixed database roles. I call them permission clusters because they can combine permissions on many database objects. For example, one fixed role (db_datareader) allows you to read data from any table in the database - not just one or two certain tables. At the second level, you can assign permissions at the level of specific database objects. SQL Server allows you to combine both methods: a user can be a member of one or more fixed database roles and also have specific permissions on specific database objects. I'll cover managing permissions on specific database objects in the next section.
Two special users can be associated with more than one login. These are the dbo and guest users. The dbo user is a member of the sysadmin fixed server role, and can create objects on the server, such as databases or database tables. A user whose login does not belong to the sysadmin fixed server role can also create database objects, such as tables.
The visibility of objects in the ADP Project Database Window depends on whether the project login belongs to the sysadmin fixed server role. If the ADP project login is a member of the sysadmin role, the Database Window displays all objects owned by the dbo user, without parentheses after their names. For dbo users, all objects owned by users other than dbo are shown with the user name enclosed in parentheses after the database object name. If the ADP project login does not belong to the sysadmin role, the names of objects owned by this user are shown in the database window without parentheses. The names of objects whose owner is dbo are shown with the suffix (dbo). Objects whose owners are not ADP project login or dbo user are not shown in the Database Window.
Most logins have users for each database they have access to. However, a login can access a database even without a user in that database if the database has a dedicated user. The name of this user is guest, which is a user for logins without database accounts. The guest user is not associated with any login. You can assign permissions to the guest user just like any other user in the database. This allows you to configure rights for logins that do not have specific users in a specific database.
Members of the sysadmin fixed server role have unrestricted rights to any database on the server. Therefore, members of this role can access the database even if the database has no users.
One of the fastest and easiest ways to grant a user rights to perform functions in a database is to assign the user to a member of fixed database roles. There are nine fixed database roles in SQL Server, and they are the same for SQL Server 7.0 and SQL Server 2000. The names and brief descriptions of the fixed database roles are shown in the following table. You can run the sp_helpdbfixedrole system stored procedure to obtain a list of fixed database role names. Calling the sp_dbfixedrolepermission system stored procedure will return a table set with specific permissions for each fixed database role.
|
Fixed database role name |
Description of the fixed database role |
| db_owner | Unlimited permissions in the database. |
| db_accessadmin | To add and remove database users. |
| db_datareader | For reading from database tables and views. |
| db_datawriter | To add (insert), edit (update) and delete (delete) records of database tables and views. |
| db_ddladmin | To execute any SQL Data Definition Language statement (or perform these functions using a GUI) on a database. |
| db_securityadmin | To manage user role membership, object access permission, and database ownership. |
| db_backupoperator | To create backup copies (backing) and restore from them (restoring) databases. |
| db_denydatareader | To deny (or revoke) permission to make any SELECT from a specific database object. |
| db_denydatawriter | To deny (or revoke) permission to any INSERT, UPDATE, or DELETE statement performed on a specific database object. |
Assigning users to fixed database roles affects the functions that users can perform. It is possible to change the impact of a user's membership in a fixed database role by assigning permissions to the user on specific database objects. For simplicity, this section doesn't cover this, but the next section covers assigning permissions to specific objects and user-defined database roles. The Database Window does not show any tables unless the user for the ADP project login is a member of the db_datareader fixed database role. A user without membership in the db_backupoperator role cannot execute the Backup or Restore menu commands (Tools menu, Database Utilities submenu). Accordingly, users who are not members of the db_ddladmin fixed database role cannot create tables or other objects in the database. Using the Database Window, a user without membership in the db_ddladmin role can edit server objects such as tables and views, or create new ones; however, edited and created objects will not be saved to the database.
Members of the db_owner fixed database role can perform all the functions provided by membership in any other database role. Because logins in this role can create databases without membership in the sysadmin fixed server role, members of the db_owner group are not required to be dbo users. Subsequently, members of the sysadmin fixed server role can perform any functions in the database that a member of the db_owner group can perform. Additionally, tables and views created by the dbo user have improved visibility compared to tables and views created by members of the db_owner role who are not members of the sysadmin fixed server role.
User-defined database roles and permission assignments
The SQL Server security specification allows six types of permissions for specific database objects. Database administrators can set permission to access specific objects by creating user-defined database roles and adding users to these groups. They can then assign permissions to specific objects in those roles. All members of any of these special roles inherit the permissions of those roles. Database administrators can add and remove users from roles after they are created, and change permissions for roles.
The six object permission names and their descriptions are shown in the following table. You can assign these permissions to specific database objects. If you want to grant permissions to all objects, you will have to assign permission to each database object. However, you are better off using fixed database roles if you need to grant permission to all database objects. For example, if you want to give SELECT permission to all tables and views (including those that have not yet been created), it is easier to assign the user to the db_datareader fixed database role. If your goal is to limit SELECT permission to only one or two existing ideas, then create a user-defined database role, grant SELECT permission on these views, and include in that role those users who need to be given special permissions.
|
Permission name |
Permission Description |
| SELECT | View records in a table or view. |
| INSERT | Add new records to a table or view. |
| UPDATE | Changing the contents of table or view records. |
| DELETE | Deleting records from tables or views. |
| REFERENCES | Allows you to create foreign keys to a primary key or unique index of a table or user-defined table function(row-returning user-defined function). |
| EXECUTE | Execute a stored procedure or user-defined function. |
Permissions to specific database objects can have three states: granted, denied, and revoked. If you grant access to an object, the permission value will have the status granted. Accordingly, if you deny access, the permission will be denied. If you are revoking a permission, change the permissions from granted or denied to revoked. Revoked permissions do not allow or deny access to an object.
Because it is possible for a user to be included in multiple fixed and user-defined database roles, permission conflicts between roles are possible. You can use these conflicts to fine-tune your security system. For example, a user is included in the db_datareader fixed database role, which gives the user the right to execute SELECT queries on all tables and views in the database. However, you can deny reading from the sales table to a user who has permission to read data from all tables in order to hide sensitive information in the sales table from him. Accordingly, members of the db_denydatareader fixed database role cannot issue SELECT queries against any table in the database, even if users are included in a user-defined database role that grants read permission from some specific tables. The denied permission always overrides the granted permission.
SQL Server makes it easier to manage security by assigning permissions to groups of users through their membership in user-defined and fixed database roles. Remember that each user usually corresponds to a specific login. In turn, logins can correspond to specific Windows users. However, one login can also be a member of the Windows user group. Therefore, if you already have a Windows group for which you want to set the same security settings, then create a login for Windows groups. Then create a user for this login. Any permissions you define for this user apply to all members of the corresponding Windows group.
SQL Server Security Programming
SQL-DMO is a container application (Automation application), the purpose of which is to administer SQL Server. Because SQL-DMO is an API for SQL Server Enterprise Manager, you can program everything in SQL-DMO that the Enterprise Manager graphical tools can provide you with, including managing all aspects of SQL Server security. This aspect of SQL-DMO is especially useful for projects using MSDE and MSDE 2000 because they do not include Enterprise Manager. Additionally, Access 2002 programmers will need to learn some software solutions for managing security, since Access 2002 does not have the ability to manage security through menus (as did Access 2000).
In Access, you can program using SQL-DMO the same way you use any other COM object. Your VBA project must use the SQL-DMO library. Microsoft SQLDMO Object Library is the name of this object library. Add a link to the library using the References command (Tools menu). The library DLL file is included with MSDE and MSDE 2000. Other versions of SQL Server include the DLL and a Help file that you can open directly from the Visual Basic Editor (VBE) window. Those using Microsoft Office XP Developer Edition can install a version of SQL Server that includes a help file for SQL-DMO.
The figure shows a fragment hierarchical model SQL-DMO with objects for code examples below. Note that the SQLServer object is located at the top of the hierarchy. Many applications can connect to SQL Server through the SQLServer object. The SQLServer object's descendants are collections of objects for logins and databases, as well as individual objects such as IntegratedSecurity, which provide the ability to manage SQL Server security. The following example uses the SecurityMode property of the IntegratedSecurity object to demonstrate how to set the SQL Server authentication mode. Members of the Databases collection are individual databases, each of which has its own collections and objects. One of these collections is the Users collection. Another collection is used for fixed and user-defined database roles (DatabaseRoles). Individual users' membership in the DatabaseRoles collection determines the users' ability to read and modify objects in the Tables and Views collections.
Drawing. A fragment of the SQL-DMO object model related to SQL Server security
Before we talk about SQL-DMO, it is important to know that the DLL for SQL-DMO is different for SQL Server 7 and SQL Server 2000. The two versions of SQL-DMO differ in at least two aspects. First, the version for SQL Server 2000 includes new objects introduced in SQL Server 2000, as well as improvements to traditional ones. SQL-DMO for SQL Server 2000 also includes traditional SQL Server 7.0 management objects. Object names for 2000 usually end with 2 (for example, SQLServer2 for new objects, instead of SQLServer for traditional ones). Second, programs that use SQL-DMO for SQL Server 2000 cannot be controlled by SQL Server 7.0 (even if the programs use traditional SQL-DMO object names). However, you can manage SQL Server 2000 by using the SQL-DMO library for SQL Server 7.0. This limitation is a result of changes in library file formats when migrating to a new version; The SQL-DMO script syntax has not changed.
The difference between versions of SQL-DMO requires careful attention to which version of the library you will use in your project. If you want your SQL-DMO application to work with SQL Server 7.0 and SQL Server 2000, you must write programs using SQL-DMO for SQL Server 7.0. On the other hand, if you need to take advantage of SQL Server 2000, then you need to develop your application using the library for SQL Server 2000. Since the article demonstrates security programming techniques using SQL-DMO, I used the SQL Server 7.0 version of SQL-DMO. Either way, the code looks the same for different versions, unless you are using new objects introduced in SQL Server 2000.
Connecting to SQL server
Just as you can connect to SQL Server in two ways using the Connection Properties dialog in an ADP project, you can connect to SQL Server in two ways through SQL-DMO. One method corresponds to authentication using SQL Server. Using this method, your code must send the server name, login, and password via SQL-DMO to the server. You can use the server name parameter to specify different instances of SQL Server on your local workstation or on another workstation on the network. SQL-DMO also allows you to connect by specifying only the server name. In this case, SQL-DMO sends the ID of the user authorized in Windows to the SQL server instance. To use this method, you must set the server's LoginSecure property to True.
The following listing shows a pair of procedures that demonstrate the syntax for connecting to an instance of SQL Server using a SQL Server login. The first procedure defines three string parameters for the server name (srvname), login (suid) and password (pwd). It then sends them to the second procedure, which begins by initializing the SQLServer object. This object represents a server instance. The second procedure then calls the Connect method of the SQLServer object. This method takes three parameters as input. The syntax for passing the variables server name, login and password is demonstrated.
| Sub CallSQLDMOSQLServerLogin() Dim srvname As String Dim suid As String Dim pwd As String "Defining arguments for SQL server login "Calling the connection procedure using the SQL server login method Sub SQLDMOSQLServerLogin(srvname As String, _ "Calling the Connect method to connect using the SQL server login method "Clearing variables The following code example demonstrates the syntax for connecting to a SQL Server instance using the Windows login. Sub CallSQLDMOWindowsLogin() SQLDMOWindowsLogin srvname Sub SQLDMOWindowsLogin(srvname As String) "Server instance "Set the LoginSecure property before calling "Clearing variables |
Changing the authentication mode
One of the major benefits that SQL-DMO provides to MSDE and MSDE 2000 programmers is that it provides a feature that would not otherwise be available to them. This is because Enterprise Manager is not included in the MSDE or MSDE 2000 installation package. For example, the SQL Server Enterprise Manager client component allows administrators graphic means change server authentication mode: Windows authentication or mixed mode. The ADP project does not provide this option. However next pair procedures will allow you to change the server authentication mode, even without being able to use Enterprise Manager.
The first procedure determines the value of one parameter and sends it to the second procedure. This parameter specifies one of two authentication modes. The procedure comments show the names of two predefined constants according to the authentication mode. Mixed mode authentication is the default mode. SQL-DMO allows you to set an authentication mode that is not available even in Enterprise Manager. In other words, you can use SQL-DMO to tell the server to accept only SQL Server logins.
The second procedure connects to the server using a Windows user ID. It then sets the SecurityMode property of the IntegratedSecurity object for the server to the value of the parameter passed to it from the first procedure. If the value of this property changes the authentication mode, the mode will not change until you stop and restart the server. However, calling the Stop method of the server object cannot immediately stop the server. You must wait until the server stops. Using the Status property of the server object, your program can monitor for a message that the server has stopped. The procedure uses a loop, waiting for the Status value to change to SQLDMOSvc_Stopped. Next, the procedure executes the Start method of the SQLServer object. Calling this method will set new mode authentication for the server.
| Sub CallChangeServerAuthenticationMode() Dim constAuth As Byte "Set constAuth to: "Set default value for constAuth "call a procedure to change the authentication mode Sub ChangeSeverAuthenticationMode(constAuth As Byte) "Set the server name; "an instance of the SQLServer object for the connection "Set the SecurityMode property for Windows "We call the command to stop and wait "Restarting the server with a mixed authentication type "Clean up |
Opening of the ADP project
Sometimes it is necessary to programmatically open an ADP project from your SQL-DMO application. ADP project opening technology does not require the use of SQL-DMO, however, your SQL-DMO application can take advantage of this capability. The last code example in this article (right before the final section) demonstrates how to create a new user. The need to open an ADP project is based on the generation of a connection string when installing an application based on an ADP project.
The following code example demonstrates how to open an existing ADP project. In the code below, the ADP project name is msdn_test_security.adp. The sample code allows you to open an ADP project in Windows authentication mode or in SQL Server authentication mode. Again two procedures are used. The list of parameters in the first procedure - CallOpenADPWindowsOrSQLServer - is relatively large, which is due to the need to specify the server name, database, path and name of the ADP project file, SQL Server login and password, as well as a logical variable. The Boolean variable determines whether the project is opened with Windows or SQL Server authentication. When specifying Windows authentication, there is no need for a login and password because the ADP project automatically references the Windows user ID. The second procedure uses these parameters to open an ADP project.
Before describing the second procedure, it would be useful to explain some features of the code. First, the procedure that opens the ADP project is already running in the Access session. Therefore, we need to create a new instance of Access for the project that we intend to open. To create an Access session, an OLE class is used.
Secondly, any existing ADP project always opens with the server connection parameters saved during the previous opening. Therefore, the program must open the project using the old connection parameters, and then change these parameters. New connection settings can only be assigned to an open project. After closing the project, the new connection parameters are saved until the user changes them in the Connection Parameters dialog. (However object model Office allows you to hide and make unavailable the Connection menu command in the File menu).
Third, a session with an open ADP project will live as long as the variable representing that session lives. Use the Public description in the session definition for the project being opened. The variable is declared in the module description section. This will allow the session to live as long as the project that opens it, that is, our code, lives. The Access session variable in the example is named appAccess, and the following declaration is in the standard module definitions section. Since the declaration must be at the top of the module, you will not see it in the code for either the first or second procedure.
Public appAccess as Access.Application
The second procedure, OpenADPWindowsOrSQLServer, begins with a message asking whether to leave the session open after the procedure completes. If the user answers "no", the procedure will set the connection parameters for the project and then close the session without a chance to view the changes. Next, the project starts a new Access session using the CreateObject function. The arguments to this function vary depending on the version of Access you are using (the code describes the specifics of using Access 2000 or Access 2002). After creating a session for a project, the procedure calls the OpenAccessProject method to add the ADP project to the session. The argument to this method is a path along with the name of the project file. The .adp extension is optional. After the project is opened, the procedure overrides its connection settings using the OpenConnection method of the CurrentProject object. The procedure redefines connections using two different connection strings depending on the value of the bolWindowsLogin variable. This Boolean variable is set by the first procedure to indicate what type of authorization needs to be set for the project. The last step of the procedure closes the session if the user answered yes to the question in the message box before opening the session.
| Sub CallOpenADPWindowsOrSQLServer() Dim srvname As String Dim dbname As String Dim path As String Dim prname As String Dim suid As String Dim pwd As String "Setting options for opening an ADP project "Calling the procedure for opening the ADP prname project Dim bolLeaveOpen As Boolean "Leave the project open after the procedure is completed? "Defining an Access session object (use .9 for Access 2000 "Open the project with the login and password of the last launch "We install new login for Windows or SQL Server authentication; "We close the session, or we stop |
Adding and removing logins
There are three ways to connect to SQL Server. These three types include one type that is checked by SQL Server and two other types that are checked by Windows. The two types that Windows checks are logins for individual user IDs and logins for groups of Windows users. If you already have a Windows user group, all of whose members need to be given the same rights to SQL server resources, you only need to set the login to the Windows group. This will save you from having to create a login for each group member.
The login creation example demonstrates the syntax for creating a standard SQL Server login and a login based on a Windows user group. The code for creating a SQL Server login for a Windows group is almost the same as the code for creating a login for an individual Windows user. Therefore, the following example of creating a login for a Windows group is exactly the same for creating logins for individual Windows users.
The example of creating logins consists of two procedures. The first procedure defines the parameters for creating a login on SQL Server, with which the second procedure will create new logins. The third procedure is a function that converts the value of an internal constant into a string describing the name of the constant.
The second procedure, after connecting to the server, initializes the login object. This object is a member of the Logins collection shown in the figure. Next, the procedure sets three parameters for the login: its name (login_name), default database (defaulf_db_name) and password (password). Simple assignment operations operate on the first two arguments. To set a password for the SQL server login, the SetPassword method of the login object is called. When setting a password for a new login, use an empty string as the first argument to the method; specify the password for the new login as the second argument. In our example, the password for login_name is defined as "password" (without quotes). Adding the login_name login is completed by calling the Add method of the Logins collection.
You can also use the SetPassword method to override the password of an existing login. The first argument of the SetPassword method in this case is the current password. However, if you do not know the current password, you can specify an empty string as the first argument, just like when creating a password for a new login. Creating or changing a login is possible if your current login (under which you connected to the server) is included in one of the fixed server roles: sysadmin or securityadmin. It doesn’t matter how you change the password: programmatically or by other means. The example uses the sa login, which is part of the sysadmin fixed server role.
The next few lines of the second procedure create a login for the Windows group named msdn_OS_users. In my case this Windows group of three Windows user IDs, in reality the Windows group could be both a user group local computer, and the Windows server domain. The login creation process begins by initializing a new login object (lgn1). If you try to reuse lgn1 without reinitializing (zeroing), a runtime error will occur reminding you that a new object is required. The procedure uses a two-part Windows group name. The first part is the Windows server name, the second is the Windows server group name. The backslash serves as a delimiter. The default database for the second login is again the your_db_name database. Since the second login is created for Windows authentication, there is no need to set a password. However, you must set the Type property to SQLDMOLogin_NTGroup. This value corresponds to an internal constant corresponding to the Windows group login. When adding a SQL Server login, you do not need to set the Type property because it is the default for SQL Server logins. The creation of the second login ends by calling the Add method of the Logins collection.
After adding two new logins, the procedure prints the names and types of all server logins. The Type property corresponds to the value of internal constants. To make the display of types readable, a function is used that converts constants into corresponding text names.
After printing the members of the Logins collection, the procedure deletes two newly created logins by name: login_name and YOUR_SERVER_NAME\msdn_OS_users. This example demonstrates the use of the Logins collection, its Remove method, and the Name property of the login that you want to remove. To confirm the deletion of two logins, the procedure displays the list of logins again from the Logins collection. Open the Immediate window and compare the two lists of logins: after adding and removing two logins.
| Sub CallLoginDemo() Dim srvname As String Dim suid As String Dim pwd As String "set parameters for login when authenticating with SQL server "Calling the procedure for creating logins Sub LoginDemo(srvname As String, _ suid As String, pwd As String) "Initializing the server object "Calling the Connect method "Initializing the login object "Adding a SQL Server login srv1.Logins.Add lgn1 "Initializing the login for reuse "Add a login for the Windows group "Output of all logins after adding two new ones "Deleting newly added logins "Re-displaying all logins "Clearing variables Function DecodeLoginType(lgn_type As Byte) As String Select Case lgn_type |
Creating a login with a user who is a member of the db_datareader role
Programmatically adding a new login and assigning it to a default database does not create a user in the database for that login. Therefore, an ADP project based on this login cannot connect to the default database. In order for the project to work with the database, you must create a user in the database for your login.
The final code example of the article demonstrates the procedure for adding a new SQL Server authentication login and creating a user for that login in the your_db_name database. In this example, the new user is also assigned to the db_datareader fixed database role. Finally, the ADP project is opened based on the new login and user.
After connecting to the server and creating a new login_name, the code creates a new user. Next, the Name and Login properties are assigned to the user. The Login property means the login under which the user connects - login_name in our example. The user is then ready to be added to the Users collection of the database. Note that the syntax for adding a new user follows the syntax for the object hierarchy. The Add method for a new user is applied to the Users collection, which in turn belongs to the database object. Database is a member of the Databases collection owned by the server object (SQLserver). After creating a user, one or more lines are required to add the user to the db_datareader fixed database role. The method for adding a user to a role is called AddMember. In our case, the AddMember method of the DatabaseRoles collection element adds a user to a database role. This method takes as a parameter the string expression of the Name property of the new user object.
Once the security configuration for the new login and user is completed, the OpenADPWindowsOrSQLServer procedure is called. The previous listings provide an example of how to use this procedure. In our case, the msdn_test_security.adp file is opened based on the login_name. To check the functionality of the login, open the your_db_name database tables. Additionally, the functionality of the new login can be checked in the Connection Parameters dialog box by specifying the login your_login.
If you try to run the MakeLoginWithDatareaderUser procedure again, the attempt will fail. This is because the login your_login already exists. An error will occur when you try to add it again. An attempt to run the procedure may fail for another reason: You have not closed the project connected to the database with this login. You can write standard processing VBA errors to handle runtime errors. Similar errors may accompany the execution of the previous examples. I chose not to go into a discussion of runtime error detection, focusing on SQL server security issues.
| Sub MakeLoginWithDatareaderUser() Dim srv1 As SQLDMO.SQLServer Dim lgn1 As SQLDMO.Login Dim usr1 As SQLDMO.User Dim srvname As String Dim suid As String Dim pwd As String Dim dbname As String Dim path As String Dim prname As String Dim bolWindowsLogin As Boolean "Defining the arguments "initialize the server "Call the Connect method for login when authenticating with the SQL server "Initialize and add the login object "Initialize and add the user object "Set parameters for opening an ADP project prpath = "Path_to_project_file" "This argument controls the use of the Windows login "Calling the project opening procedure prname "We leave the object open for viewing |
Conclusion
SQL Server security is built on different principles than those used by Jet database programmers. However, SQL Server Books Online carefully documents SQL Server security guidelines. This article translates the basic BOL rules into specific rules whose purpose is to use an Access project with SQL Server. The code examples in this article demonstrate how to manage SQL Server security using Access.
