Exercise 2: Creating a Standard SQL Server Login in Visual C#

Generator UPCA in Visual C# Exercise 2: Creating a Standard SQL Server Login

In this exercise, you create a standard SQL Server login. 1. Launch SQL Server Management Studio (SSMS), and open a new query window. 2. Enter the following code:

CREATE LOGIN sqltest WITH PASSWORD = 'P@55w0rd'

3. Inspect the login to verify that you have created a standard SQL Server login as well as the properties that are set by default for this login.

6

SQL Server 2005 is secure by design, secure by default, and secure in deploy ment. If a feature is not required to run the core database engine, it is disabled by default. Additionally, multiple steps and permissions are necessary to allow code running within the engine expanded authority. The SQL Server Configuration Manager must be used to manage SQL Server services. Proxy accounts can be created for the SQL Server Agent that allow a job step the appropriate security access to a subsystem.

You can use the following questions to test your knowledge of the information in Les son 1, Designing Instance-level Security. The questions are also available on the companion CD if you prefer to review them in electronic form.

Answers to these questions and explanations of why each answer choice is right or wrong are located in the Answers section at the end of the book.

1. You are loading an assembly into SQL Server that has to read and write files on the operating system. What level of CAS should you give the assembly A. SAFE B. UNSAFE C. EXTERNAL_ACCESS 2. Contoso Limited has hired a new security administrator who will be responsible for adding new logins to the SQL Server but does not have the authority to grant permissions or add the logins to a database. Which server role should the secu rity administrator be added to in order to perform her job A. sysadmin B. processadmin C. serveradmin D. securityadmin

After you have been authenticated by SQL Server and have a connection, you will want to begin accessing databases. Before doing so, you have to be granted the author ity to access a database along with permissions on objects. This lesson explains the options for securing a database and any objects contained within a database.

After this lesson, you will be able to:

Specify database users. Design schema containers for database objects. Specify database roles. Define encryption policies. Design DDL triggers.

To access resources within a database, the login being used by an application or user needs to be granted access to the database. To grant access to the database, you add the login as a user in the database by executing the CREATE USER command, which has the following general syntax:

[ { { FOR | FROM }

[ WITH DEFAULT_SCHEMA = schema_name ]

When adding a user to a database, the user is normally mapped to a login, certifi cate, or asymmetric key. Although the name of the database user can differ from the principal it is mapped to, using different names can create confusion and provide self-documentation, so it is usually not done.

6

You can add each user in a database to one or more database roles. SQL Server ships with 10 database roles, as shown in Table 6-1. You can also create your own roles within a database.

Table 6-1

Role db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader db_denydatawriter db_owner db_securityadmin public

Description Adds or removes users in the database Backs up the database (cannot restore) Issues select operations against all tables within the database Issues insert, update, and delete operations against all tables within the database Executes data definition language (DDL) commands Denies select operations against all tables Denies insert, update, and delete operations against all tables Owner of the database with full control over all objects Manages role membership and permissions Default group that every user belongs to