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…
Category: Database Admin
Articles related to the administrating SQL Server 2008 R2.
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…
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…
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…
Full Text Search – Part 3
Tonight, I want to revisit the [WILD LIFE] database that we were using last time. The main goal is to correctly search the [ANIMALS] table returning all [NAME]’s that contain the word fly. We want the query plan associated with our query to use a index to speed up overall execution time. The first solution to this business problem resulted in FULL TABLE SCANS or CLUSTERED INDEX SCANS. Please see the desciptions of graphical icons that can show up in a query plan. Since every record in the table has…
Full-Text Search – Part 2
I want to revisit the [WILD LIFE] database that we were using last time. First goal is to create a full text index (FTI) using the SQL Server Management Studio (SSMS) graphical user interface (GUI). Second goal is to have our SELECT query perform a INDEX SEEK using the FTI. We have the existing [ANIMALS] table with a identity column called [ID] and a data column called [NAME] which is loaded with 445 animals names that I grabbed from WIKIPEDIA. The idea is to search for animals that have ‘fly’…
Database & Object Ownership
The most ANNOYING thing is to come across a database that is still owned by the user that created it. In prior server versions, this was a big deal since users owned objects and ownership had to be changed before the user could be removed from the system. Therefore, if user named ford created a table named brands, the fully qualified table name would be ford.brands. In SQL Server 2005 and greater, this has changed with a default schema of dbo. All objects created in this schema are owned by…
Searching Stored SQL – Part 4
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…