SQL Server database administration using Web Data Administrator

Introduction

Your SQL Server database can be administered from the web. This is done using an application called Web Data Administrator. Web Data Administrator can be used for setting up the database including setting user permissions down to the table level. If you need to import data or otherwise modify the database you may find this easier using SQL Server database administration using Access Project.

The database owner (dbo) is the personal username of the person registered as the database owner. The dbo is the user who can and should control who has access to what on the database. The dbo can request additional logins to the database (see Application to register additional users for MS SQL Server Databases) and can set permissions on the database for the different users who have been given login rights to the database, using web data administrator. When a database is created, the Web Information Provider username with which each database is associated is created as a database reader.

Before using this application you will need to have

a) obtained a Web Information Provider username and password from Request for Web Site name or Web Information Provider:
b) filled in the Application for a MS SQL Server Database form requesting a database and received confirmation that it has been created on the SQL Server, and
c) enabled Windows Authentication as follows:-

Enabling Integrated Windows Authentication

You should be aware that in order for users to be able to authenticate to modify a database through web pages, you need to be using Microsoft Internet Explorer (version 6.0.2800.1106 with SP1 is recommended - please ensure you have downloaded the latest updates). You also need to ensure that your browser is set to use Windows Integrated Authentication. To do this, carry out the following:-

  1. Open Internet Explorer
  2. Select the Tools menu, then Internet Options... and click on the [Advanced] tab
  3. Scroll down to the Security options and make sure that the box for Enable Integrated Windows Authentication is checked.
  4. Once you have enabled Windows Authentication, you will need to restart Internet Explorer before the setting will take effect.

Accessing Web Data Administrator

To use Web Data Administrator there is no need to download or install any software. It is accessed using the University's own network. Use the web page 'Connect to MS SQL Server Web Database Administrator' at and follow the link to the login page.

Enter the Server name vi2kwsdb1, make sure that Windows Integrated authentication is selected and click on [Login].

Please note that your login to the SQL Server will expire if you do not use it for a time. If this happens, just login again.

You should now see a list of all the databases to which you have access.

Please note that whilst you will be able to see the databases master, msdb and tempdb, you do not have any permissions on these tables and attempting to edit them will cause you errors.

Also note that your database has been created for you and it will be on the list. You do not have permissions to create additional databases - IT Services will do this for you via the Application for a MS SQL Server Database form. Attempting to create your own databases will cause an error.

Warning

WARNING: As the owner of the database you have permissions to set - and are responsible for - the access permissions of users to the database. By default only you as database owner have permissions to access the database. All other users of the database have to be added by you and you set the permissions for each of these users. As described below, to enable anonymous users to access your database via the web, the account used by anonymous users, i.e. iusr_vi2kws1, should be included by you.

Using your database

As the 'dbo' (database owner) of your database you will have full control.This includes the ability to actually delete your database - please do NOT do this as it may affect your access to the server itself and you will no longer be able to login. If you do wish to have a database deleted, please email a request to ITS Help. It can then be processed correctly so that your access to other databases that you may have on the server is not affected.

In addition, whilst you will be able to view the Properties of your database, you will not be able to change the settings for Data Files and Transaction Logs.

Requesting a server login for a new user

If you wish to give access to your database to a user who does not have a login to the SQL Server, please ask them to complete the online Application to register additional users for MS SQL Server Databases form for an additional login.

Adding users and setting access permissions

Please note that you can only add users to your database and set their access permissions if the user has been given login rights to the SQL Server, as described above.

  1. Click on the name of the database to which you wish to add an user.
  2. Click on [Users].

  1. Click on [Create new user].
  2. Select the Login Name from the drop-down list (rdg-home accounts only) and click on [Create User].

  1. Select the required permissions for the user and click on [Save User].

  1. The new user will now appear under the list of Users.

If you wish to change the permissions for a user, click on [Edit].

If you wish to remove a user from your database, click on [Delete].

Important: you must add the local IUSR_VI2KWS1 user if you want your database to be read from the web with anonymous access: other users can be added as needed. Groups can also be added, for example RDGStaff and RDGPGrads.

Once you click on Create User you will be given a list of permissions that you can allocate to your newly created user.

Creating new tables
  1. Click on the name of the database to which you wish to add a table.
  2. Click on [Tables].
  1. Click on [Create new table].
  2. Enter a name for your table and click on [Create].
  1. Enter the required values for the column and click on [Add].
  1. Continue to add columns as required by clicking on [Create new column].

Further help on use of Web Data Administrator

There is a 'help' feature within the application. To access this, simply click on the 'Help' icon (?) at the top right of the screen.

Logging out of Web Data Administrator

To log out, simply click on the 'Logout' icon (X) at the top right of the screen.

TM Trademark owned by Microsoft Corporation.
© Screen shot(s) reprinted by permission from Microsoft Corporation.