PERL was originally written by Larry Wall while he was working at NASA’s jet prepulsion laboratories. It’s syntax is a combination of many different languages. While PERL is not a acryonymn, one of Larry’s favorites is ‘Pratical Extraction and Reporting Language’. The first version of PERL was release in December, 1987. Getting to the main question, ‘Why use PERL?’. Here are a few reasons why people use the language. It’s free! The PERL language can be installed on any UNIX box for free. The community version of Active State Perl…
Tag: free code
Various Triggers – Part 6
Triggers can be used to track Data Manipulation Language (DML) changes that occur in tables. Today, I am going to expand the AUTOS database script to add table auditing. Auditing is a very good practice when multiple people have rights to make changes to important semi-static data. This is not a good solution if the amount of data is large or number of table changes are huge. First, I am going to leverage the newly built schema called Audit Database Tracking (ADT) to seperate data tables from audit tables. A…
Upgrading Older Databases
A new version of SQL Server is released every few years. This year was no different with the launch of SQL Server 2012 in March. It is important to migrate older, unsupported databases to a current version. I am going to examine 3 different techniques to upgrade the [PUBS] sample database from SQL Server 2000 to 2008 R2. The first two techniques require the database files to be copied from the older server to the new one. Use the ALTER DATABASE command to kick off the users and set the…
Various Triggers – Part 5
Another usage of Database Triggers is track Data Definition Language (DDL) changes that occur in a database. Today, I am going to enhance the AUTOS database script to add such auditing. This auditing is a very good practice when multiple people have syadmin rights to make such changes. First, I am going to create a schema called Audit Database Tracking (ADT) to seperate the data tables from audit tracking tables.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-size:small;">-- Delete existing schema. IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT') DROP SCHEMA [ADT]; GO -- Create a ADT schema CREATE SCHEMA [ADT] AUTHORIZATION dbo; GO </span> |
Second, I am going to create a table to hold the keep track of the DDL changes that are…
Protecting the [sa] Account
During the installation of SQL Server, you are prompted to select an authentication mode. Regardless of the choice, a [sa] account will be created. If you are using Windows Authentication, the account will be be left in a disabled state. On the other hand, if you are using Mixed Mode Authentication, you are asked for a password and the [sa] account will be left in an enabled state. Because the sa account is well known and often targeted by malicious users, do not enable the [sa] account unless your application…
Various Triggers – Part 4
Another usage of Data Modification Language (DML) triggers is the replicating data for historical tracking or data aggregation. Today, I am going to expand on the AUTOS database that I created in prior articles to demonstrate such usage. The business unit has asked us to track each macro task in the making of an automobile. To accomplish this goal, I am going to add the following objects to the AUTOS database. STATUS_CODE – a coded version of the status of a given task. MAKE_STATUS – for a given vehicle…
RunAs SSMS VB Script
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…