Introduction to Databases and Microsoft Access 8/8

Managing SQL Server User Accounts:
SQL Server Roles
How to use the SQL Server
Create Login Wizard

Creating and configuring a database user ID

Introduction to Data Replication:
What is data replication?
Why bother about data replication?
How SQL Server 7.0 replicates data

* SQL Server Roles:

A role is a grouping of users who have similar SQL Server access needs. Roles can be broadly defined under:

·        Predefined server roles
These are pre-configured, and defined at the SQL Server level, not at the database level, which means that anyone who belongs to any of these roles has specific permissions to manage SQL Server and all the databases managed by SQL Server. They include:
* System Administrators (sysadmin): Can perform any task with SQL Server, including overruling any of the other predefined server roles.
* Database Creators (dbcreator): Can create and alter individual databases.
* Server Administrators (serveradmin): Can configure serverwide settings

·        Predefined database roles
these roles are specific to databases, and don’t cross databases. They include:
* Database Owner (db_owner): Has ownership permission of a database and can perform an configuration or maintenance tasks within a given database; can override any of the other roles.
* Database Data Reader (db_datareader): Can view any data from all the table objects in a database.
* Database Data Writer (db_datawriter): Can insert, modify or delete any data from all the table objects in a database.
* Database Access Administrator (db_accessadmin): Can manage database user Ids for a database.

·        The public role
When created, every database has the public role by default. What is unique about this role is that every database user ID in a database belongs to this role. You cannot add or remove users from the public role, and any permissions assigned to the public role automatically are assigned to all database user Ids in the database.

·        Custom database roles
In general, it is convenient to take advantage of as many of the built in roles as possible, but SQL Server also gives you the capability to create your own custom database roles.

* How to use the SQL Server Create Login Wizard

1.    Start the SQL Enterprise Manager using a SQL Server ID that belongs to the sysadmin’s role. This displays the SQL Enterprise Manager’s main screen.

2.    Click the SQL Server Tools drop-down menu and select Wizards. This displays the Select Wizards dialog box.

3.    Click on the plus sign next to Database to display all of the wizards that are applicable to databases, including the Create Login Wizard. To start the Create Login Wizard, click on it, and then click OK. This displays the first screen of the Create Login Wizard.

4.    After you have read the screen, click Next to continue. This displays the Select Authentication Mode screen.

5.    A login ID can be created using either Windows NT authentication or SQL Server authentication if you are in the SQL Server Mixed Authentication Security Mode, or only using Windows NT Authentication if you are in the Windows NT Server Authentication Security Mode. In this example (figure to be added later), SQL Server is set up for the SQL Server Mixed Authentication Security Mode, and both authentication options are available.
Normally, you will want to select the Windows NT Account Information I Use to Logon to my Computer option. This is because most of your users will already have NT Server user accounts and you will want to use them as your SQL Server login IDs. If any of your users will not have NT Server user accounts, you can choose the SQL Serve Login Information That was Assigned to Me by the System Administrator option.

6.    A) If you chose Windows NT Authentication for this login ID and are using the screen displayed below (to be added later), you must enter
i) The domain name and NT Server account name that you want to use as the login ID for SQL Server. You must enter both the domain name and username, separated by a backslash.
ii) Under Security Access, you must select from either Grant access to the Server or Deny Access to the Server. In almost all cases, you will choose the first option.
B) If you selected SQL Server Authentication for the login ID, and you are using the following screen (to be added later), you must enter a login ID and a password.
After you have completed screen A or B, click Next to continue. This displays the Grant Access to Security Roles dialog box.

7.    This screen enables you to assign this login ID to one or more predefined server roles. Check the selected roles, and click on Next.

8.    This screen designates which databases will be accessible to this login ID. What it does is automatically enter the user’s login ID as the database user ID for each database you select in this screen. Click Next to continue.

9.    This is the final screen of the Create Login Wizard. It summarises all the choices you made. If you want to make any changes, you can click the Back button and make any changes, or click Finish to end the Wizard and create the login IDs.

* Creating and configuring a database user ID

1.    Start SQL Enterprise Manager using an account with Sysadmin privileges.

2.    Using your mouse in the Scope pane, select the SQL Server you want to work with and open its folder to display all the SQL Server’s options.

3.    Click the pus sign next to the Databases folder in the Scope pane so that all the databases managed by SQL Server are displayed.

4.    Now click the plus sign next to the database folder where you want to create the new database user ID.

5.    Right-click the Database User folder and select New Database User from the pop-up menu. This displays the New User dialog box.


6.    From the Login Name list box, select the pre-existing login ID you want to grant access to this database. This list box will only include login names that have yet to be granted access to this database. After you select the login name, the User Name box is automatically filled in for you.

7.    The Database Role Membership scroll box contains a list of all the database roles for this database. Select all the roles that you would like to apply.

8.    Click OK to create the database user ID. The New User dialog box disappears and the newly created database user ID appears in the Details pane.

* What is data replication?

Data replication in its simplest form is copying data from its source to a destination or multiple destinations. Data replication is different from data distribution since data distribution is manual, while replications are autonomous. After data replication is configured, no intervention is required by the Database Administrator.

* Why bother about data replication?

Data replication allows data to be replicated across the room or across the world automatically. Without data replication, multiple sites would have slow access to their data. Having multiple sites around the world with the same data, load balancing becomes a reality, and the data is always reachable.

* How SQL Server 7.0 replicates data:

SQL Server 7.0 replicates databases based on the ‘publish and subscribe’ model. In brief, if you are a Publisher, you are a server that makes its data available for replication to other servers. The Publisher specifies which data is to be replicated and flags changes made to the data since the last synchronisation process occurred. To make sure this data integrity is maintained, any data element that is replicated has one and only one Publisher. Subscribers are servers that store the replicated data and receive updates from publishers.

A Publication is simply a collection of Articles, and an Article is a grouping of data to be replicated. This can be an entire table, specific columns of a table, specific rows of data, or a combination of both. When you subscribe, you subscribe to Publications – not individual articles.