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.
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.
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.
Adds a new user to the database
Adds a Windows NT user or group
Changes a user's password
Changes a user's default database
Adds a new role to the current database
Adds a new member to the role
Removes a user from a role
Lists the columns for the specified table
Lists the tables in the current database
Changs the default database for a user
To view a full range of system stored procedures, please see Books online.
Open a code Module.
In the VBE, select Tools References.
Select Microsoft SQLDMO Object Library and check the checkbox.
Click OK to set the reference.
That's it - the reference is set and you're ready to go.
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
In this example, we will create some code to connect to SQL Server and list all the available databases.
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
Remember to set a reference to Microsoft SQLDMO library.
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:
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:
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:
Within an ADP we can also execute a system stored procedure, which will return similar information.
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:
Select Create Text Stored Procedure.
Replace the template with the following:
ALTER PROCEDURE usp_listtables AS Exec sp_databases
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.
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 :
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.
Not only can we work with tables and columns, we can also use SQLDMO to work with 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:
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.
Once given permission to access the server, you must then create a user with specific database permissions.
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.
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 .
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
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.
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:
Has the highest level of control on all databases
Can manage user and group security
Can read data from all tables within a database
Can change and add data to the database
Can change or remove objects
Can manage database roles and permissions on objects
Can backup the database
Cannot select data
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.
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.
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
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