Microsoft SQL Server 2005 For Dummies Cheat Sheet - dummies
Cheat Sheet

Microsoft SQL Server 2005 For Dummies Cheat Sheet

From Microsoft SQL Server 2005 For Dummies

By Andrew Watt

Getting around in Microsoft SQL Server 2005 means knowing not only the basics, but also new security features. Use SQL Server 2005 to store information for personal or business use. Knowing how to constrain values and build reports will help you get the most out of this relational database management system.

New Security Features in SQL Server 2005

Microsoft SQL Server 2005 has several new security features compared to the SQL Server 2000 security model. The SQL Server 2005 security model enables you to both assure security and make SQL Server easy to use for authorized users and customers.

Here’s a look at new or updated security features in SQL Server 2005:

  • Logins: Logins are (SQL Server) instance-level principals.

  • Users: Users are database-level principals.

  • Separation of users and schemas: Each schema has a user who is the owner of the schema. A schema is the owner of the objects in the schema. It is possible to change the owner of a schema without having to change any application code that uses objects in that schema. This avoids the problems that occur if a user who owns database objects, for example, leaves the company.

  • Catalog security: Metadata is visible only for the tables that a user has permissions on. This helps to hide unauthorized information from users.

  • Module execution context: This supplements Ownership Chaining that was present in SQL Server 2000.

  • Granular permissions control: Granting of permissions is more granular than in SQL Server 2000. This means that you can now use lower privilege accounts to do some tasks for which you needed to use an administrator account in SQL Server 2000.

  • Password policy enforcement: If you run SQL Server 2005 on Windows 2003 Server, you have the option to enforce in SQL Server any password policy that already exists for the Windows user accounts.

How to Constrain Values in SQL Server 2005

Constraints (rules enforced by SQL Server 2005) offer a key way to ensure data integrity. SQL Server 2005 enables you to constrain the values allowed in a particular column so that inappropriate data is not permitted.

SQL Server 2005 supports five types of constraint:

  • Not null: When this constraint is specified, you are not allowed to have a field in a column with no data in it.

  • Check: This constraint specifies allowed values. For example, you might want age of employees to be between 16 and 65.

  • Unique: This constraint specifies that you can’t repeat the value in a field in any other field in the same column.

  • Primary key: This constraint specifies a unique identifier for the value in a field, which uniquely identifies a row.

  • Foreign key: This constraint references a unique identifier in another table in the database.

(The datatype in the Table Designer also constrains the values allowed in a column but is not considered to be a constraint.)

When you create a table, you typically add a primary key to the table. A table may have only one primary key.

If you do not designate a column (or combination of columns) as the primary key when you create the table, you can specify a primary key later, provided certain conditions are met. A column intended as a primary key can’t have NULL values and each value in the column must be unique. If the table has data and those conditions are not met, your attempt to create a primary key on that column causes an error and no primary key is created.

Choose a primary key when you first create a table. By doing so, you avoid possible time-consuming changes to a table after it contains data.

If you want to change the column (or combination of columns) that is the primary key, you need to delete the primary key constraint on the original column(s) and add a primary key constraint. If the original primary key is referenced by a foreign key constraint, you must delete the foreign key constraint first, and then delete the original primary key constraint.

To add a check constraint, follow these steps:

  1. Right-click the row that specifies the relevant column.

  2. Choose Check Constraints from the context menu.

  3. In the Check Constraints dialog box, click the Add button to add a new constraint.

  4. Click the ellipsis in the Expression row of the right pane of the Check Constraints window.

    The Check Constraints dialog box opens.

  5. Enter an expression that specifies the constraint.

    The allowed expressions depend on the datatype of the column. For example, in an Age column, you can specify that allowed ages are between 16 and 70 by using the following expression:

Age >= 16 AND Age <= 70

How to Use Report Builder in SQL Server 2005

Report Builder, a tool new in SQL Server 2005, enables information workers to create their own ad hoc business reports. Before an information worker can create a report in Report Builder, a developer (you) must create a report model in Business Intelligence Development Studio. Here’s how:

  1. Open Business Intelligence Development Studio.

  2. Choose File→New→Project.

    The New Project dialog box opens.

  3. Select Business Intelligence Projects in the left pane.

  4. Select Report Model Project in the right pane.

You then go on to select business data that the end user needs to design a report. The Visual Studio solution that you create is deployed to a Reporting Service server.

Users use Report Manager to access the Report Server. From Report Manager, the user clicks to download, install, and run Report Builder. In Report Builder, the information worker then manipulates a user-friendly representation of the report model to create table, matrix, or chart reports.

When you choose from the available report models, the Report Builder displays a design surface. You can drag items from the Explorer to the design surface to specify an ad hoc report. To view the report, click the Run Report button and the report is displayed.