There is a problem you might encounter when restoring a database which has database users attached to it.  They will lose their mapping to their respective server login, and this will prevent you from logging into the database.  In this short article, I’ll show you how the problem is created, and how you can fix it.

Creating the Problem

As usual, I’ll start by attaching a copy of the AdventureWorks database to my local SQL Server instance.  Next, I’ll create a server login mapped to a database user called dbtestuser.

Now, make a backup copy of this database, upload it to your hosting account, and perform a restore operation using the DiscountASP.NET Control Panel.  If you log into your DiscountASP.NET database using SQL Server Management Studio and expand both Security Folders, you’ll notice the database user was restored but not the server login.

Normally, this isn’t an issue since you can just delete the database user and re-create the server login and database user.  We even provide this Knowledge Base article to assist you; however, one customer had this dilemma where his database users had permissions already assigned to objects in his database, and he wanted to keep them.  Recreating the logins was not an option in this case.

We will go ahead and create a server login with same name as the database user (in this case, dbtestuser) through the DiscountASP.NET Control Panel.

But when you right click on the server login dbtestuser, select Properties, and then User Mapping in SQL Server Management Studio, you’ll notice it’s not mapped to a database user.

If you check the box and try to map it to the database user through the SQL Server Management Studio GUI, you will get an error.

Unfortunately, the mapping cannot be restored using the GUI tools.

The Solution

The solution is very simple.  After you have created the server login through the DiscountASP.NET Control Panel, you can just open a New Query window and execute the system stored procedure sp_change_users_login or use the ALTER USER command to restore the mapping.  I’ve included sample syntax below:

sp_change_users_login N’Update_One’, N’dbtestuser’, N’dbtestuser’

ALTER USER dbtestuser
WITH DEFAULT_SCHEMA = dbo,
LOGIN = dbtestuser

Now, when you right click on the server login dbtestuser, select Properties, and then User Mapping, it will show that it’s mapped correctly, and you will be able to log into your database.

Here’s a message from one of our customers, and I thought it was a great idea:

“I have discovered the problem. The database was full. Can I suggest that in future you implement a warning mechanism when approaching maximum usage of things like Database, Bandwidth etc. This would be very useful, even if it was just an automatic email sent out. Thanks.”

We actually do already have a process in place to warn you if you exceed your monthly bandwidth, but we don’t have one in place for databases because it is difficult to automate the process on a grand scale.

It would mean we would have to scan every database and make sure the email addresses we have on file are current and active, and that’s not always the case.  It’s not a good use of computer resources or time, and not every customer needs it.  So I am going to provide you with a custom solution!

That’s the beauty of the DiscountASP.NET hosting platform.  We give you the tools you need to create your own solution.  You may not realize that if we were to automate some processes it could lead to less freedom (more restrictions) in the way you can build or manage your application.

It’s a difficult balancing act to implement solutions and features that are useful, effective, and efficient.  Anyway, this solution can be implemented individually with relatively a few lines of code.

First, you’ll need to launch SQL Server Management Studio and log into your DiscountASP.NET database to create a stored procedure.  Click on the New Query button and copy the following code into the window:

CREATE PROCEDURE [dbo].[IsDatabaseAlmostFull]
AS
BEGIN
  DECLARE @CurrentSizeMB int
  DECLARE @MaxSizeMB int
  DECLARE @PercentageFull int
  SET NOCOUNT ON
  SET @PercentageFull = 0.90 /* Change this value */
  SELECT @CurrentSizeMB = size*8/1024,
         @MaxSizeMB = max_size*8/1024
  FROM sys.database_files
  WHERE [file_id] = 1
  IF @CurrentSizeMB >= (@MaxSizeMB * @PercentageFull)
    RETURN 1 /* True */
  ELSE
    RETURN 0 /* False */
END

The only value you need to change is on line 08.  The @PercentageFull variable determines at what percent the database fills up that you will get notified.

In the above example, you will get an email notification when your database reaches 90% full.

Hit F5 to execute the T-SQL code.

You can go ahead and close SQL Server Management Studio and launch Visual Studio.

Then go to File -> New -> Website…  I’m more of a C# programmer, so under Templates, I’ll select Visual C# and choose ASP.NET Empty Web Site.  You can also name the folder if you’d like next to Web location.

Click the OK button to continue.

On the right hand side under the Solution Explorer window, right click the folder name and select Add -> Add New Item…  Select Visual C#, Web Form, give the file a name if you’d like with a .aspx extension and make sure the option Place code in separate file is checked.

Click on the Add button.

Under the Solution Explorer window, expand the file name with a .aspx extension to reveal the code-behind file which should end with a .aspx.cs extension.

Double click it to open the file.

Paste the following code in the code-behind file replacing only the connection string and mail settings (lines 14 – 30).  Select Build -> Build Solution to compile your code and save your settings.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
      String connectionString = @"Data Source=tcp:sql2k###.discountasp.net;Initial Catalog=SQL******_######_databasename;User ID=SQL******_#######_databasename_user;Password=*******;";

#region Mail Settings
// Settings used to authenticate and construct email message
// Hostname of your SMTP server
String smtpHostname = @"smtp.YourHostedDomainName.com";
// Set to 25 or 587 depending upon your email provider's instruction
int portNumber = 25;
// From address and account used to provide SMTP authentication
String fromAddress = @"[email protected]";
// Password for email account used to provide SMTP authentication
String emailPassword = @"*******";
// Address of email account that will receive the notification
String toAddress = @"[email protected]";
// Name of your database used to construct subject and body of email
String dbName = @"SQL******_######_databasename";
#endregion

 SqlConnection dbConnection = new SqlConnection();
 dbConnection.ConnectionString = connectionString;
 dbConnection.Open();

 SqlCommand command = new SqlCommand();
 command.CommandText = @"dbo.IsDatabaseAlmostFull";
 command.CommandType = CommandType.StoredProcedure;
 command.Connection = dbConnection;

 SqlParameter returnValue = command.Parameters.Add("RetVal", SqlDbType.Int);
 returnValue.Direction = ParameterDirection.ReturnValue;

 command.ExecuteNonQuery();

 int isFull = (int)returnValue.Value;

 dbConnection.Close();

 if (isFull == 1)
 {
 MailMessage confirmationMail = new MailMessage();
 confirmationMail.From = new MailAddress(fromAddress);
 confirmationMail.To.Add(toAddress);
 confirmationMail.Subject = @"Warning regarding " + dbName;
 confirmationMail.Body = @"Warning.  Database " + dbName + @" is nearing max capacity.";

 SmtpClient smtp = new SmtpClient(smtpHostname, portNumber);
 NetworkCredential credentials = new NetworkCredential(fromAddress, emailPassword);
 smtp.Credentials = credentials;
 smtp.Send(confirmationMail);
 }
 }
}

Now upload your files to your hosting account using an FTP client like FileZilla.

Then launch the Scheduled Task Manager in the DiscountASP.NET Control Panel and setup a scheduled task for every 15 minutes that will invoke the code.

That’s it.

You will now receive an email notification whenever your database reaches or exceeds the percentage value you set in the stored procedure.

For those of you who know what you’re doing and are lazy like me, you can download the code files here and just adjust the settings I’ve mentioned above using any text editor.  I’ve included the .aspx, .aspx.cs, and .sql file for your convenience.