SQL Server and more

Read more about the article Logging changes to sys.configurations
Microsoft SQL Server

Logging changes to sys.configurations

Some server wide settings can have major negative impact if they are changed in your SQL Server environment. So as a DBA it is a good practice to monitor changes to these setting, and to raise alerts when they are changed. To do this, the system view sys.configurations can be monitored for changes.

This blog post will demonstrate a way to monitor SQL Server configuration settings. The solution will also give you get the complete history for the different settings in sys.configurations.

Note that database mail needs to be configured for the current solution to work properly.

(more…)

Continue ReadingLogging changes to sys.configurations
Read more about the article Deny updates on a table but allow inserts
Microsoft SQL Server

Deny updates on a table but allow inserts

Sometimes it is nessesary to deny updates on a table, but allow inserts. This is the case when the history must never be changed, for instance in a table with accounting data.

This can be done in two different ways. The first method is to use permissions on the table and make sure everyone is denied update permissions on the table. The other method is to use an “instead of update” trigger.

Here I’ll show both methods.

(more…)

Continue ReadingDeny updates on a table but allow inserts
Read more about the article Adding a Workflow step in Automate BPA Server
Automate BPA Server

Adding a Workflow step in Automate BPA Server

When designing workflows in Automate BPA Server, I like to “draw the big picture” first. That is, lay out the different major steps in the workflow and adding details and logic to them later.

Problem is, you can’t just add an embedded workflow to an existing workflow without getting errors.

(more…)

Continue ReadingAdding a Workflow step in Automate BPA Server
Read more about the article List SQL server roles by users
Microsoft SQL Server

List SQL server roles by users

SQL Server roles can be used to give users server wide privileges. The server roles are sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin. The mapping between users and server roles can be found in the system view sys.server_role_members.

Here I’ll show a SQL script that lists all users and their server roles.

(more…)

Continue ReadingList SQL server roles by users
Read more about the article A replacement for syslogins
Microsoft SQL Server

A replacement for syslogins

The system table sys.syslogins was marked as deprecated in SQL Server 2005, and is included up until 2012 for backwards compability only.

So what is the replacement of sys.syslogins? As it turns out, there is no exact match in SQL Server 2005 and later. Instead there are various system views that can be combined to gather the same information.

I created a SQL statement to combine those system views into a single view, matching the old sys.syslogins.

(more…)

Continue ReadingA replacement for syslogins
Read more about the article Copy logins between SQL Server instances
Microsoft SQL Server

Copy logins between SQL Server instances

If you are using SQL Server log shipping (or mirroring), you probably want to copy the logins from the primary server to the secondary.

When databases are log shipped, the users go along with their respective databases. But those users are connected to a login on the primary server. Those logins are not copied automatically to the secondary. Users without connection to a login are called orphaned users.

This has the effect that users may not be able to login on the secondary in case of a failover.

One solutions to this problem is to transfer logins manually. That may be the best solution if there are very few logins to manage. But if there are more than just a few logins, an automatic solution is required.

There are many methods available to handle this. In this blog post I’ll combine a couple of the best solutions available, and add a few features that I found missing.

(more…)

Continue ReadingCopy logins between SQL Server instances
Read more about the article Tables larger than tempdb
Microsoft SQL Server

Tables larger than tempdb

If your database contains very large tables, querying or managing those tables may fill tempdb. When tempdb is full, an error is raised and the transaction is rolled back. If this will happen or not depends on how large your tempdb can get. Even if the size of tempdb isn’t limited by turning off autogrowth or by setting a max size, the disk space available is the upper limit for how big tempdb can get. Check out this blog post to see how to calculate the maximum possible size of tempdb.

Large tables can be a latent problem, since there may be no problems at all until someone, for instance, tries to sort the table in a query. SQL Server uses tempdb (as the name implies) as a temporary workspace for data, and if SQL Server can’t handle all the data in memory tempdb is used.

To avoid this problem you need to know if there are very large tables that won’t fit in tempdb when at its maximum size. Here is a script to look for tables larger than tempdb:

(more…)

Continue ReadingTables larger than tempdb
Read more about the article Tempdb max size
Microsoft SQL Server

Tempdb max size

There is always a limit on how large tempdb can get. Even if there are no size limitations on the data files in tempdb, the limit is still the free space on disk drives. Here I’ll show how to calculate the maximum size of tempdb (data only, not log).

(more…)

Continue ReadingTempdb max size