A seperation of power between a day-to-day user account and a special occasion adminstrator account can be beneficial for a DBA. By usig a less privledge account for daily activities can avoid costly human mistakes. A mistake will be denied due to not having the correct privledges. It is standard to use Microsoft Windows Domain Accounts for daily adhoc queries. A Standard SQL Account is usually used by custom applications to connect and perform work in the database. How does one easily switch from one windows account to another? A…
Various Triggers – Part 3
A typical use of Data Modification Language (DML) triggers is to prevent unwanted data modification. AFTER triggers are great at detecting the INSERT, UPDATE or DELETE action and rolling back the changes. A good security model can prevent these types of changes and should be the first line of defense. However, there are cases in which keys to the castle have to be given out for political reasons inside the organization. In that case, this type of preventive programming can be handy. Today, I am going to revisit the AUTOS…
Various Triggers – Part 2
I am going to wrap up my discussion of LOGON TRIGGERS. We now have a MS ACCESS database that is using a LINKED TABLE pointing to the [WILDLIFE] SQL Server database. Most professional applications set the application name string before connecting via ODBC API to a data source. MS ACCESS does just that at a higher level, the product suite name. The trigger named ‘DISALLOW_MS_OFFICE’ uses the APP_NAME() system function to determine if the application is MS OFFICE product. We are going to revoke all MS OFFICE products from connecting…
Access Linked Tables
Today, I am going to build upon the prior ODBC Connection article by showing you how to create a LINKED TABLE in MS ACCESS. I am assuming that you have created a blank MS ACCESS 2007 database named [WILDLIFE.accdb]. I will be using the [WILD LIFE] SQL Server database that was created in a earlier article as the source of our information. First step is to select the external data, more, ODBC database option from the menu. This launches the wizard which walks you thru creating a LINKED TABLE. Second…
ODBC Connection
The ODBC standard was developed in the early 1990’s to allow a software developer to use a single interface to access mutltiple data sources. Microsoft Access is a very versatile application since it can import or link to a variety of ODBC data sources. Today’s article will cover how to define a new ODBC connection. I will be using the [WILD LIFE] database that was created in a earlier article. First step is to open to the administrator tools on the windows operating system. You can find this group under…
Renaming A Server
I was recently approached by the WINTEL to help rename a Windows 2003 Server that has Symantec Backup Exec 12.5. This backup software uses the Express Version of SQL Server 2005 to manage information about backups, restores, and schedules. Since I have not done this task in a while, I wanted to write down the steps for future requests. The first task is to connect to the Express Version of SQL Server 2005 via my locally installed SQL Server Management Studio tool. This can be accomplished by using the following…
Various Triggers – Part 1
I am going to wrap up my discussion of LOGON TRIGGERS. The number and types of database triggers available to database administrators has increased over the years as the Microsoft SQL Server database engine has matured. There are now triggers for execution after both DDL and DML modifications. These triggers can be classified as AFTER – TSQL code to be executed after an action or INSTEAD OF – TSQL code to be executed in lue of a action. Triggers are commonly used in following tasks: Auditing data changes by place…
Database Files – Part 3
DATABASE SNAPSHOTS were added to the SQL Server product in the 2005 version. They are great for saving a version of the database before major data changes happen and perfect for rolling back the changes if they are not successful. While books online suggest other uses, I think this one is the best. Please see the limitations of snapshots before creating one. I am going to explain how to create a database snapshot, how to revert back to a snapshot after corrupted occurs, and how to drop a database snapshot.…
Database Files – Part 2
The most basic Microsoft SQL Server database is composed of two operating system files: a primary data file and a transaction log file. If possible, the data and log files should be on different physical disks. Today, I am going to be re-creating the [WILDLIFE] database using this simple design in preparation for talking about MOVING and COPYING database files. The key CREATE DATABASE, CREATE TABLE and CREATE INDEX statements are below. The complete script which catalogs ANIMAL names is enclosed as well as today’s new examples. If you have…
Database Files – Part 1
A Microsoft SQL Server database is composed of at least two operating system files: a primary data file and a transaction log file. I am going to be talking about how to define databases with multiple files tonight. There are three types of operating system files. PRIMARY data file is the starting point of the database and is a pointer to any other data files that are allocated. A extension of .mdf is used to identify these files. This data file can contains system tables and user defined objects as…