When you have No GUI

There are several third-party tools available on the Internet ranging from freeware to expensive proprietary solutions. However, all is not lost and there are ways in which you can work with both SQL Server and MSDE via code.

SQLDMO

SQL Distributed Management Objects (SQLDMO) is an object model we can reference from an ADP to work with SQL Server and MSDE. In order to work with this object model, you must set a reference to it in your project. We'll show you how to do this now.

System Stored Procedures

In addition to SQLDMO there are many system stored procedures designed to return information about your server and its databases. System stored procedures are supplied with SQL Server and can be executed from the client interface. Where appropriate, we will demonstrate both the SQLDMO method and the system stored procedure when working with database objects.

All system procedures are prefixed as sp_procedurename and are stored in the master database on SQL Server.

Security Procedures

Procedure Name	Description
sp_addlogin	Adds a new user to the database
sp_grantlogin	Adds a Windows NT user or group
sp_password	Changes a user's password
sp_defaultdb	Changes a user's default database
sp_addroleAdds	Adds a new role to the current database
sp_addrolemember	Adds a new member to the role
sp_droprolemember	Removes a user from a role

System Management

Procedure	Description
sp_columns	Lists the columns for the specified table
sp_tables	Lists the tables in the current database
sp_defaultdb	Changs the default database for a user

To view a full range of system stored procedures, please see Books online.

To Set the Reference to SQLDMO

1. Open a code Module.

2. In the VBE, select Tools References.

3. Select Microsoft SQLDMO Object Library and check the checkbox.

4. Click OK to set the reference.

That's it - the reference is set and you're ready to go.

Important 

For the rest of this section, we will be using the Microsoft Access database, which will contain the examples and the example code, IceAdmin .

In the remainder of this chapter, we will show you how you can:

• Connect to SQL Server

• View databases and database objects

• Amend database objects

Try It Out-Connecting to SQL Server and Displaying the Available Databases

In this example, we will create some code to connect to SQL Server and list all the available databases.

1. Enter the following into the code window:

      Dim IceServer As New SQLDMO.SQLServer     Dim dbs As New SQLDMO.Database     IceServer.LoginSecure = True     IceServer.Connect "HOME"     For Each dbs In IceServer.Databases     Debug.Print dbs.Name     Next   

   Important 

   Remember to set a reference to Microsoft SQLDMO library.

   Note that the name of the server may differ .

2. Running the above code will list the available databases on the HOME SQL Server to the Immediate window.

How It Works

The Dim statements should be familiar by now, so we will look at the other new items:

 IceServer.LoginSecure = True 

Because we're using Windows Integrated security we tell the procedure this by setting the LoginSecure property to True . If we were using SQL Server Security, then we would have to change the syntax and pass a username and password to the procedure. For example:

 IceServer.connect ,servername,username,password 

Many book examples show a blank password value for examples like this. Do not leave passwords blank on any installation of SQL Server. Many hackers use this blank password as a means to get into the server. In addition users have been known to play around with logins. You do not want an unauthorized user to login into your server as an sa or system administrator. Unless of course you like working weekends!

Using the Connect property of the Server object, we pass the name of our SQL Server to the procedure:

 IceServer.Connect "HOME" 

The next statement uses a For Each loop to iterate the available databases on the named server, printing them out to the Immediate window:

 For Each dbs In IceServer.Databases Debug.Print dbs.Name 

We then use the Next statement to move through the available databases in the collection and print the name of each out:

 Debug.Print dbs.Name 

Within an ADP we can also execute a system stored procedure, which will return similar information.

Try It Out-Executing a System Stored Procedure

The sp_databases system stored procedure returns information on the databases on a SQL Server. The procedure will return the database name, database size , and remarks. This data is held within the SQL Server system tables. For this example, we are simply going to execute the stored procedure from within another stored procedure. From the Database window:

1. Click Queries.

2. Click New.

3. Select Create Text Stored Procedure.

4. Replace the template with the following:

      ALTER PROCEDURE usp_listtables     AS     Exec sp_databases   

5. Execute the procedure responding to the Save As... prompt.

In addition to listing the databases, we can also view the tables in a specific database. Again we will use both methods to view tables and their columns.

Try It Out-Listing Columns and Tables

Again we will first use SQLDMO to list the tables in the Icecream SQL Server database. The code to do this is very similar to that used to list the databases. The only changes we need to make are to loop through the table collection for a specific database rather than loop through the database collection:

   Sub listtables()     Dim IceServer As New SQLDMO.SQLServer     Dim IceTable As New SQLDMO.Table     dbName = "Icecream2002SQL"     IceServer.LoginSecure = True     IceServer.Connect "HOME"     For Each IceTable In IceServer.Databases(dbName).Tables     If IceTable.TypeOf = SQLDMOObj_UserTable Then     Debug.Print IceTable.Name     End If     Next     End Sub   

How It Works

As before we are using a trusted connection to SQL Server:

 IceServer.LoginSecure = True 

Then connecting to the server known as HOME :

 IceServer.Connect "HOME" 

We then use a For Each loop to iterate through the tables. In doing so, we use the SQLDMOObj_UserTable property to check that the table is a user table and not a system table. If it is a user table, we print it out to the debug window.

When executed, the above code will list all the user tables within the Ice Cream SQL Server database to the Immediate window.

SQLDMO and Security

Not only can we work with tables and columns, we can also use SQLDMO to work with security.

A Note on Security

When you leave the world of Jet behind, you also leave behind Microsoft Access's security model and move into the world of SQL Server security. SQL Server security is much more sophisticated than that of Access, but also has much in common with it. Security on the server is multi-layered and comprises:

Logins

Users must have a valid login to the server. This can be either a Windows login or a SQL Server login. Windows Authentication is supported by default. When working in a total Windows Operating System environment this is the recommended way to proceed; however, some concerns have been expressed about how connection pooling is handled by SQL Server, which can lead to performance problems.

Users

Once given permission to access the server, you must then create a user with specific database permissions.

Roles

Roles are defined job functions within SQL Server. For example, all clerical staff could be assigned to a clerical role. If you are using Windows security with SQL Server, than it is possible to assign a Windows group to a role.

SQL Server 2000 can use two types of security model:

• Windows Authentication, which maps all users and groups to Windows accounts

• A mixed model, where you can use both Windows and SQL Server security to manage security

For all examples in this chapter, we will be using Windows Authentication.

Important 

A full discussion of security is outside the scope of this chapter, but it is an area you must get familiar with before you make any application live. SQL Server Books Online provides information on the various security issues and the latest security information can be found at http://microsoft.com/sql/default.asp .

Try It Out-Adding a Login to SQL Server

Creating the login and user with SQLDMO is a three step process:

• Connect to the server

• Create a new login

• Add the new login to the server

1. Enter the following procedure into a new module in the module window. Remember you need a reference set to SQLDMO for this to work.

      Sub IceLoginAdd()     Dim IceServer As New SQLDMO.SQLServer     Dim newlog As SQLDMO.Login     Dim login_name As String     Dim password As String     Dim default_databse As String     default_database = "Icecream2002SQL"     password = "mypassword"     login_name = "Ice1"     IceServer.LoginSecure = True     IceServer.Connect "HOME"     Set newlog = New SQLDMO.Login     newlog.Name = login_name     newlog.Database = default_database     newlog.SetPassword "", password     IceServer.Logins.Add newlog     Set newlog = Nothing     IceServer.Disconnect     Set IceServer = Nothing     End Sub   

How It Works

The first step is to dimension all our variables before use. This process should be very familiar to you by now:

 Dim IceServer As New SQLDMO.SQLServer Dim newlog As SQLDMO.Login Dim login_name As String Dim password As String Dim default_databse As String 

The next line may, however, be new to you:

 default_database = "Icecream2002SQL" 

When they login into SQL Server, users are given access to a default database. In this case, the default database for this login is our upsized IceCream database. Failure to set a default database could lead to the user accessing the master database used to manage SQL Server. You need to be careful that this does not happen.

Next we assign the password and login for this user to the variables password and login_name:

 password = "mypassword" login_name = "Ice1" 

Note that plain text has been used for the password for illustration only. When creating passwords in SQL Server use strong passwords, that is, a mixture of text and numbers .

Finally we create the new login with password:

 Set newlog = New SQLDMO.Login newlog.Name = login_name newlog.Database = default_database newlog.SetPassword "", password 

Once done, we clean up after ourselves by resetting the server and closing the connection. That's the login created. Now we need to create a user for the database. In this case, we are going to add our new login ICE1 to the database as a user.

The above example uses Mixed Security to establish the login. However, it's also useful to know how to carry out the same procedure for Windows Authentication. In this next example, we will use most of the same code, only this time we will create a login for an existing Windows User. Before we look at adding users, let's have a very quick overview of database roles.

Database Roles

Roles within SQL Server allow you to group individuals who require the same permissions into specific named roles. For example, you may have a group of users who can only read data from the database, or others who have both read and write permissions. You could simply group your users together and assign them to a role with read-only permissions. However, this gives you very little flexibility, unless, of course, this is a requirement for that group of users. In addition, if you have a low number of users you may find that creating a role for, for example, two of the users to be too much bother, and it is much simpler to give permissions to individual user. Database Roles are particularly useful when you have a large number of users and they make the management of permissions on database objects very straightforward.

SQL Server 2000 comes with some database roles already in existence. We will be using these for our examples. The default roles are:

Role	Comment
db_owner	Has the highest level of control on all databases
db_accessadmin	Can manage user and group security
db_datareader	Can read data from all tables within a database
db_datawriter	Can change and add data to the database
db_ddladmin	Can change or remove objects
db_securityadmin	Can manage database roles and permissions on objects
db_backupoperator	Can backup the database
db_denydatareader	Cannot select data
db_denydatawriter	Cannot change data

Once we create a login to the server, we can then create a user account for the specific database and assign our user to one of the existing database roles. In order to create the new user, we need to create a new user object and add the user to its collection. Once this is done, we then add our user to one of the fixed database roles. This fits into the three- tier model of security: create the login to the server, then create the user, and assign the user to a specific database. Once we grant the user permission to access the database, we must then provide access to the objects within that database. In this case we are doing that via a database role.

Important 

All database users are by default members of the public role and you cannot delete this role. What you can do, however, is to set permissions for this role. In that way a basic set of permissions on database tables and objects is available by default for every user added to the database.

Try It Out-Create the Whole Works

For this example we are going to:

• Create a server login

• Create a user

• Assign the user to a database

• Assign the user to a default database role

1. Right let's go! Enter the following code into a module:

      Public Sub Createsecurity()     Dim databasename As String     Dim loginname As String     Dim username As String     Dim DbRole As String     Dim default_databse As String     Dim iceserver As SQLDMO.SQLServer     Dim logIn As SQLDMO.logIn     Dim IceUser As SQLDMO.User     'Connect to the Icecream Databse on SQL Server     'We are using Windows security so we set login to true     Set iceserver = New SQLDMO.SQLServer     iceserver.LoginSecure = True     iceserver.Connect "HOME"     'Make sure the new login is to the correct default database     default_database = "Icecream2002SQL"     'Add the login     Set logIn = New SQLDMO.logIn     logIn.Name = "Martin"     logIn.SetPassword "", (reid01)     iceserver.Logins.Add logIn     'That's the login to the database server created.     'The default database is set to the IceCream example     'Now we must create the user account     Set IceUser = New SQLDMO.User     IceUser.Name = "MReid"     IceUser.logIn = logIn.Name     iceserver.Databases(default_database).Users.Add IceUser     'Now assign the user to a fixed database role     'Set up our user to be a data reader only     DbRole = "db_datareader"     iceserver.Databases(default_database).DatabaseRoles(DbRole).AddMember     IceUser.Name     End Sub   

How It Works

We have already seen the login code, and the details on connecting to the database. We shall therefore only look at creating the user, and assigning them to a database role. This is done with the following code:

 Set IceUser = New SQLDMO.User IceUser.Name = "MReid" 

The first line of this code adds the user to the Users Collection of the database. We then assign the username MReid to the new user. The following two lines then assign the user to one of the fixed database roles, in this case db_datareader, which permits the user to read data from any table within the Ice Cream SQL database.

 IceUser.logIn = logIn.Name iceserver.Databases(default_database).Users.Add IceUser