Introduction
Sometimes, you may need to restrict the user from logging into SQL Server. This can be done in several ways and this article discusses two main techniques:
- Using a logon trigger to prevent logging into SQL Server
- Using a database specific procedure to control login into the database
The variations using logon trigger are typically used when the user authentication is done by the SQL Server. The procedural approach can be used in many kinds of situations, even if the connecting login account is always the same from SQL Server point of view. Of course, these examples can be modified in several ways to achieve the desired logic.
The topics discussed are:
First Things First, A Few Warnings
We’re going to play with logon triggers so there’s always a risk that the trigger will not perform as expected. In such case, the trigger may prevent you completely from logging into the SQL Server. In this kind of situation, you need a Dedicated Admin Connect (DAC) in order to be able to connect to SQL Server and to modify or drop the trigger. To establish a DAC, see How to Establish Dedicated Admin Connection (DAC) to SQL Server.
Another thing is that preventing the logon should not be confused with authentication. The methods discussed in this article only take additional steps for allowing or preventing to continue with the already successful logon. If you want to know more about handling authentication, please refer to Authentication in SQL Server.
The third thing is that the examples in this article partially rely on the information provided by the client application. This also means that the information may not be reliable so the techniques work typically only for the applications you have control over. In other words, when you want to control in what situations your application may or may not connect to your database, these examples may come in handy.
The Test Program and the Test Database
In order to do some testing, I prepared a small WPF program. The idea is that you can start two of these applications and try different connection variations in different kinds of situations. What comes to the actual logic, the type of the client doesn't matter. Client technology could well be ASP.NET, Windows Forms or even a console application.
Before we start, we also need the database and a few logins. These can be created as follows:
USE master;
CREATE DATABASE LogonTest
CREATE LOGIN LogOnUser WITH PASSWORD = 'DontForgetThis1';
GO;
GRANT VIEW SERVER STATE TO LogOnUser;
GO;
CREATE LOGIN NormalUser WITH PASSWORD = 'RememberMe2';
GO;
USE LogonTest;
GO;
CREATE USER NormalUser FOR LOGIN NormalUser;
GO;
The first login, LogOnUser is only used to execute the logon trigger so it has no other function. The other one, NormalUser is used for actually connecting to the database, just like a normal user would. This is why their privileges differ from each other:
LogOnUser has VIEW SERVER STATE privilege in order to be able to query sys.dm_exec_sessionsNormalUser is added as a user to our test database in order to be able to connect to it
Using a Logon Trigger to Allow Only One Connection Per User from an Application
Now let’s try the first variation. If we need to allow only a single simultaneous connection per user from our test program, the trigger could look like this:
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO
CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
@connectioncount int;
BEGIN
SELECT @connectioncount = COUNT(*)
FROM sys.dm_exec_sessions a
WHERE a.is_user_process = 1
AND lower(a.program_name) = 'login tester'
AND a.original_login_name = ORIGINAL_LOGIN();
IF (@connectioncount > 1) BEGIN
ROLLBACK;
END;
END;
GO
Few things to notice: The trigger itself is a special trigger which is created ON ALL SERVER …. FOR LOGON. This means that the trigger is server scoped and reacting for logon action. Also, the trigger is defined EXECUTE AS 'LogOnUser' so the privileges at execution time are interpreted from LogOnUser.
The logic in the trigger itself is simple; Count how many sessions are currently connected to the SQL Server from this specific program and using the same login name. If the connection count is more than one, our connection is at least the second one. In this case, the transaction is rolled back which signals to the SQL Server that we have revoked the connection. This prevents the client from establishing the database connection.
The code for connecting the database looks like this:
internal override bool Connect(string hostName) {
System.Data.SqlClient.SqlConnection connection = null;
bool ok = true;
try {
connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
connection.Open();
this.ActiveConnection = connection;
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
CloseAndDisposeConnection(ref connection);
ok = false;
}
return ok;
}
Actually, that’s not all the code needed for connecting but at this point, let’s focus only on connection attempt. The rest of the code can be found in the download. If the connection succeeds, the program shows the active connection and if it fails, a message box is shown.
So if you start two instances of our test program and in both instances provide correct SQL Server name and connect using Normal connection and Client 1, the first one should succeed but trying the same from the second program running, it should fail giving the following message: "Logon failed for login ... due to trigger execution"
Connection Pool Affecting the Connections
Ok, that was a success but if you continue to play with the program:
- Close the connection from program 1
- Try to connect using program 2
You still get the same message even though it seems that you’re not connected to the server in the first place. Well, that’s actually not true. While program 1 is still running, the connection you originally established is still alive, it’s just located in the connection pool. If you would end the program, the connection pool would be cleared.
To affect the situation, you have the "Clear all connection pools" – button. Once the connection in program 1 is closed, press the Clear button and try connecting from program 2, it should now succeed.
In our test case, the connection pool was affecting in an undesired way but it’s not the case always. Think about the client application architecture where you:
- connect
- do some operations
- disconnect
- wait for user input
- connect
- do some operations
- disconnect and so on...
In this situation, when waiting for user input, another session could take the connection and our next attempt could fail. Because of this, it’s reasonable that the connection is kept alive and reserved, not to mention the performance aspects.
Using a Logon Trigger to Allow a Connection Only From a Single Client at Any Given Time
Now let’s make a slight modification. If we want to allow multiple connections for the same login but only from a single client computer at any given same time, let’s modify the trigger.
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO
CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
@connectioncount int,
@host1 nvarchar(128),
@host2 nvarchar(128),
@message nvarchar(2000);
BEGIN
SELECT @connectioncount = COUNT(*),
@host1 = MIN(a.host_name),
@host2 = MAX(a.host_name)
FROM sys.dm_exec_sessions a
WHERE a.is_user_process = 1
AND lower(a.program_name) = 'login tester'
AND a.original_login_name = ORIGINAL_LOGIN()
AND EXISTS( SELECT 1
FROM sys.dm_exec_sessions b
WHERE b.is_user_process = 1
AND b.program_name = a.program_name
AND b.host_name != a.host_name);
IF (@connectioncount > 1) BEGIN
ROLLBACK;
END;
END;
GO
The logic is almost the same as previously. I’ve just modified the query to look for connections from different host names. Again if more than one is found, terminate the logon attempt.
Now the client side needs adjustments. As said in the beginning, we are relying on the information the client sends when connecting. In the test program, you can choose if you want to use Client 1 or Client 2 for the connection. Both client names are almost the same, they only have a different client number in the beginning.
But to keep the example as close to real world situation as possible, the program also uses the MAC address of the client computer to identify itself. The MAC address is fetched with the following code:
internal static string MacAddress() {
var macQuery =
from netInterface in
System.Net.NetworkInformation.NetworkInterface.GetAllNetworkInterfaces()
where netInterface.OperationalStatus ==
System.Net.NetworkInformation.OperationalStatus.Up
&& netInterface.NetworkInterfaceType !=
System.Net.NetworkInformation.NetworkInterfaceType.Loopback
&& netInterface.NetworkInterfaceType !=
System.Net.NetworkInformation.NetworkInterfaceType.Tunnel
orderby netInterface.Id descending
select netInterface;
return macQuery.FirstOrDefault().GetPhysicalAddress().ToString();
}
The idea is that the program uses an address that is operational as it is not a loopback address. In actual situation, you would want to add more checks to the query, for example that the address is meaningful and not an empty string.
Now again, if you connect from both running test programs using Client 1 name, this should succeed. However if you connect using Client 1 name from program 1 and using Client 2 name from program 2, the latter should fail the same way as previously.
So now, we cannot connect from two clients at the same time if they have a different host name. Again, if you close the connection and try to connect from the other one, remember to clear the connection pool in order to succeed.
Communicating with the Client from the Logon Trigger
Okay, we can restrict the logon, but the client always gets the same message. This message doesn’t really explain why the connection is rejected. In some cases, it would be nice to notify the client about the reason. However, this is not possible using traditional mechanisms such as RAISERROR or PRINT. From MSDN:
Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log.
That’s a pity but we just have to think of something else. One strategy could be that we have a folder which is shared so that it can be seen by both the SQL Server and the application. If we create a small message file in this folder, we can write the reason there for the client to fetch and show.
The CLR procedure example used in this article is simplified, so if interested, you can read more about writing into a file from the SQL Server in Writing into a file from database. It also explains the steps required to create and register the procedure.
The CLR procedure used in this example looks like this:
public class Logger {
[Microsoft.SqlServer.Server.SqlProcedure]
public static void WriteLine(string fileName, string line) {
System.IO.FileStream logStream;
System.IO.StreamWriter logFile;
using (logStream = new System.IO.FileStream(fileName,
System.IO.FileMode.Append,
System.IO.FileAccess.Write,
System.IO.FileShare.ReadWrite)) {
using (logFile = new System.IO.StreamWriter(logStream)) {
logFile.WriteLine(line);
logFile.Flush();
logFile.Close();
}
}
}
}
The code is simple, write the line of text to the provided file.
After compiling the logger project, we can register the procedure to SQL Server like this:
ALTER DATABASE LogonTest SET TRUSTWORTHY ON;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'WriteLine')
DROP PROCEDURE WriteLine
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Logger')
DROP ASSEMBLY Logger
GO
CREATE ASSEMBLY Logger
FROM ' ……\LoginTestExe\Logger\bin\Debug\Logger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE PROCEDURE WriteLine (@fileName nvarchar(2000), @line nvarchar(2000))
AS EXTERNAL NAME Logger.Logger.WriteLine;
GO
Since we’re going to use the procedure from the logon trigger, we need to do a few things:
- add the
LogOnUser to the database users - grant privileges to execute the procedure to
LogOnUser
Like this:
USE LogonTest;
GO
CREATE USER LogonUser FOR LOGIN LogonUser;
GO
GRANT EXECUTE ON LogonTest..WriteLine TO LogonUser;
GO
So now, we can make a small adjustment to the logon trigger. See the following:
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO
CREATE TRIGGER RestrictLogons
ON ALL SERVER WITH EXECUTE AS 'LogOnUser'
FOR LOGON AS
DECLARE
@connectioncount int,
@host1 nvarchar(128),
@host2 nvarchar(128),
@message nvarchar(2000);
BEGIN
SELECT @connectioncount = COUNT(*),
@host1 = MIN(a.host_name),
@host2 = MAX(a.host_name)
FROM sys.dm_exec_sessions a
WHERE a.is_user_process = 1
AND lower(a.program_name) = 'login tester'
AND a.original_login_name = ORIGINAL_LOGIN()
AND EXISTS( SELECT 1
FROM sys.dm_exec_sessions b
WHERE b.is_user_process = 1
AND b.program_name = a.program_name
AND b.host_name != a.host_name);
IF (@connectioncount > 1) BEGIN
SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126)
+ ': ' + @host1 + ': Simultaneous connection from ' + @host2;
PRINT @message;
EXEC LogonTest..WriteLine @fileName='C:\TEMP\ConnectionRejectionInfo.txt', @line=@message
SET @message = CONVERT(nvarchar(100), SYSDATETIME(), 126)
+ ': ' + @host2 + ': Simultaneous connection from ' + @host1;
PRINT @message;
EXEC LogonTest..WriteLine @fileName='C:\TEMP\ConnectionRejectionInfo.txt', @line=@message
ROLLBACK;
END;
END;
GO
The code is basically the same as previously but with two additions:
- The message why the connection was refused is written to SQL Server log because of the
PRINT command - The same message is written into a file named ConnectionRejectionInfo.txt in C:\TEMP folder
The information from which clients the connection exists is written from the point of view of both clients so that whichever client was trying to establish the connection, a message targeted for it can be found in the file. Another approach could be to rely on the fact that the latter connection is always the failing one, but this may not apply if the logic for rejecting connections is different.
To handle the situation on the program side, let’s have a look at the code making the connection:
internal override bool Connect(string hostName) {
System.Data.SqlClient.SqlConnection connection = null;
bool ok = true;
string[] logMessages;
System.Collections.Generic.List<LogMessage> messageList;
LogMessage myMessage;
try {
connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
connection.Open();
this.ActiveConnection = connection;
} catch (System.Exception exception) {
logMessages = System.IO.File.ReadAllLines(@"C:\TEMP\ConnectionRejectionInfo.txt");
messageList = new System.Collections.Generic.List<LogMessage>(logMessages.Length);
foreach (string line in logMessages) {
messageList.Add(new LogMessage(line));
}
var msgQuery = from msg in messageList
where msg.MessageTime != null
&& msg.Host == hostName
orderby msg.MessageTime descending
select msg;
myMessage = msgQuery.FirstOrDefault();
System.Windows.MessageBox.Show(myMessage != null ?
myMessage.Message : exception.Message,
ConnectionBase.Title);
CloseAndDisposeConnection(ref connection);
ok = false;
}
return ok;
}
If the connection fails, the contents of the shared file is read and parsed using LogMessage class. After that, the correct message is found based on client name and the latest timestamp. This message is shown to the user.
Let’s test: If you first connect using Client 1 from program 1 and then using Client 2, take a normal connection with fetch message functionality, you should receive a message like the following:
The error message can also be found in SQL Server log
Remember that in order for this to work, you need to have a C:\TEMP folder and have access to it or modify both the assembly and the trigger to use some other file. In real situation, you'd use a folder which would be shared to the network and it could be located for example on a file server instead of the database server.
Use a Database Specific Logon Procedure
It’s not always feasible or possible to use logon triggers. For example, think about a consolidated SQL Server instance where several different databases are located. You may not (and should not) have access to server wide functionality and even if you would, there’s no guarantee that creating a logon trigger wouldn’t affect the programs using other databases.
A simple solution is to move the logic in the logon trigger to a procedure and always when connecting to the database, call the procedure.
First, let’s remove the logon trigger so that it does not affect further testing.
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'RestrictLogons')
DROP TRIGGER RestrictLogons ON ALL SERVER
GO
Now the T-SQL code for the procedure could look like this:
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'CheckConnection')
DROP PROCEDURE CheckConnection
GO
CREATE PROCEDURE CheckConnection
WITH EXECUTE AS 'LogOnUser'
AS
DECLARE
@connectioncount INT,
@host VARCHAR(128),
@user VARCHAR(128),
@msg VARCHAR(128);
BEGIN
SELECT @connectioncount = COUNT(*)
FROM sys.dm_exec_sessions a
WHERE a.is_user_process = 1
AND lower(a.program_name) = 'login tester'
AND a.original_login_name = ORIGINAL_LOGIN()
AND EXISTS( SELECT 1
FROM sys.dm_exec_sessions b
WHERE b.is_user_process = 1
AND b.program_name = a.program_name
AND b.host_name != a.host_name);
IF (@connectioncount > 1) BEGIN
SELECT TOP 1
@user = a.login_name,
@host = a.host_name
FROM sys.dm_exec_sessions a
WHERE a.is_user_process = 1
AND lower(a.program_name) = 'login tester'
AND a.original_login_name = ORIGINAL_LOGIN()
ORDER BY a.login_time;
SET @msg = 'User ' + @user + ' already connected from ' + @host;
RAISERROR(@msg, 15, 1);
RETURN;
END;
END;
GO
GRANT EXECUTE ON CheckConnection TO NormalUser;
GO
The logic is basically the same as previously, but now we can use RAISERROR since the context where we are executing the procedure is the successfully established connection. The calling side looks now like this:
internal override bool Connect(string hostName) {
System.Data.SqlClient.SqlConnection connection = null;
bool ok = true;
try {
connection = new System.Data.SqlClient.SqlConnection();
connection.ConnectionString = ConnectionBase.GetConnectionString(hostName);
connection.Open();
using (System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand()) {
command.Connection = connection;
command.CommandText = "CheckConnection";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
this.ActiveConnection = connection;
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
CloseAndDisposeConnection(ref connection);
ok = false;
}
return ok;
}
After a successful connection, the procedure is called and if it succeeds, continue with the program. Otherwise, show the message why the connection failed.
Use a Database Specific Logon Procedure Without Visibility to Server-Wide System Views
If we take a bit more restricted situation where you have to deal with the fact that no server wide services can be used, we have to have some other kind of alternative. Again, this could be the situation in a consolidated environment where you have no privileges to system views and you can use only information within the dedicated database.
Previously, we could check existing logons from sys owned views but if we’re not allowed to do this, we have to maintain logon information ourselves. The problem is that if we just create a table and think that we:
- insert a row into it when we enter the database
- delete it when the program ends
We probably end up in problems very soon. Think about a situation where the program terminates abnormally. In such case, the program isn't able to clean the information from the logon table and when trying to reconnect, we would already have an “active” connection and basically no way to know if the information is accurate or not.
In general, I recommend avoiding reading uncommitted data, but in this specific case, the SQL Server’s ability to read information from an ongoing transaction comes in handy. This happens by using a WITH (NOLOCK) hint in the SELECT statement. As mentioned, in normal cases, this may result in unwanted results so if you have more interest, read What should be considered when NOLOCK hint is used.
So the idea is: We first log in to the SQL Server and then read the connect reservations from the reservation table and ignoring the locks. If there is no conflicting connection, we write a new reservation into the table using a transaction started on client side. After this, the client continues with normal operations BUT the transaction is kept open by the program as long as needed to ensure the connection. When the program ends, the transaction is rolled back and the connection holding the transaction is closed.
Why so complicated? The reason is that by using a transaction, we ensure that if the program crashes, the transaction is automatically rolled back by the SQL Server and we have no leftovers in the reservation table.
The table to hold the reservations could look like this:
CREATE TABLE ConnectReservation (
ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
Host VARCHAR(128) NOT NULL,
Program VARCHAR(128) NOT NULL,
UserName VARCHAR(128) NOT NULL,
ConnectTime DATETIME NOT NULL,
ProcessId SMALLINT NOT NULL
);
GO
And now the procedure would be like this:
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ReserveConnection')
DROP PROCEDURE ReserveConnection
GO
CREATE PROCEDURE ReserveConnection AS
DECLARE
@host VARCHAR(128),
@user VARCHAR(128),
@msg VARCHAR(128);
BEGIN
SELECT @host = cr.Host,
@user = cr.UserName
FROM ConnectReservation cr WITH (NOLOCK)
WHERE cr.UserName = ORIGINAL_LOGIN()
AND cr.Host != HOST_NAME();
IF (@host IS NOT NULL) BEGIN
SET @msg = 'User ' + @user + ' already connected from ' + @host;
RAISERROR(@msg, 15, 1);
RETURN;
END;
INSERT INTO ConnectReservation (Host, Program, UserName, ConnectTime, ProcessId)
VALUES (HOST_NAME(), APP_NAME(), ORIGINAL_LOGIN(), SYSDATETIME(), @@SPID);
END;
GO
As you can see, the procedure makes a simple select to check if a connection from other host already exists. As previously explained, the key is WITH (NOLOCK) hint. In case the row is found, an error is raised, otherwise the reservation is inserted.
If we have a look at the client side, it’s a bit different than the ones before.
private StandardConnection ActiveConnection;
private System.Data.SqlClient.SqlConnection ConnectReservation;
private System.Data.SqlClient.SqlTransaction ReservationTransaction { get; set; }
internal override bool Connect(string hostName) {
bool ok = true;
try {
this.ConnectReservation = new System.Data.SqlClient.SqlConnection();
this.ConnectReservation.ConnectionString =
ConnectionBase.GetConnectionString(hostName);
if (!ReserveConnection(this.ConnectReservation)) {
this.ConnectReservation.Dispose();
return false;
}
this.ActiveConnection = new StandardConnection();
if (!this.ActiveConnection.Connect(hostName)) {
ConnectionBase.CloseAndDisposeConnection(ref this.ConnectReservation);
ok = false;
}
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
ok = false;
}
return ok;
}
private bool ReserveConnection(System.Data.SqlClient.SqlConnection connection) {
bool ok = true;
try {
connection.Open();
using (System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand()) {
command.Connection = connection;
this.ReservationTransaction = connection.BeginTransaction();
command.Transaction = this.ReservationTransaction;
command.CommandText = "ReserveConnection";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
ConnectionBase.CloseAndDisposeConnection(ref connection);
ok = false;
}
return ok;
}
override internal bool CloseConnection() {
if (this.ActiveConnection != null) {
this.ActiveConnection.CloseConnection();
}
if (this.ReservationTransaction != null) {
this.ReservationTransaction.Rollback();
this.ReservationTransaction = null;
}
CloseAndDisposeConnection(ref this.ConnectReservation);
return true;
}
So when connecting, the following is done:
- A transaction is started.
- The
ReserveConnection procedure is called. - If successful, the connection containing the active transaction is stored.
- The actual connection to do database operations is created.
In this scenario, it’s critical to do the actual operations using a separate connection from the one that holds the transaction open. Otherwise, we would commit or rollback the transaction for the reservation at some point of program execution.
Use a Program Authenticated User With Transactional Reservation
One thing we haven’t touched yet is the user authentication. In all of the examples, we’ve used the actual login name from SQL Server for user identification. The login could be either Windows authenticated or SQL Server authenticated but still authenticated by the database server. What if the situation is that the same login is used for different users and the user is actually identified by the program.
In this article, I won’t go into the details about proper user authentication when done by program so I just take it that it’s done properly. The authentication step would precede the connect reservation so you would probably have a nice little procedure to check that the user is known and the hashed password matches the hash stored in the database.
What comes to connect reservation the modification is very small. Instead of using system function ORIGINAL_LOGIN(), we pass the user name to the procedure:
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ReserveConnectionWithUser')
DROP PROCEDURE ReserveConnectionWithUser
GO
CREATE PROCEDURE ReserveConnectionWithUser @user varchar(128) AS
BEGIN
SELECT @host = cr.Host
FROM ConnectReservation cr WITH (NOLOCK)
WHERE cr.UserName = @user
AND cr.Host != HOST_NAME();
IF (@host IS NOT NULL) BEGIN
SET @msg = 'User ' + @user + ' already connected from ' + @host;
RAISERROR(@msg, 15, 1);
RETURN;
END;
INSERT INTO ConnectReservation (Host, Program, UserName, ConnectTime, ProcessId)
VALUES (HOST_NAME(), APP_NAME(), @user, SYSDATETIME(), @@SPID);
END;
GO
In this variation, the reservation both check and made is made using the supplied user name.
Also in the program, the change is small, we just pass the user name as a parameter to the procedure:
private bool ReserveConnectionWithUser(System.Data.SqlClient.SqlConnection connection,
string userName) {
bool ok = true;
try {
connection.Open();
using (System.Data.SqlClient.SqlCommand command =
new System.Data.SqlClient.SqlCommand()) {
command.Connection = connection;
this.ReservationTransaction = connection.BeginTransaction();
command.Transaction = this.ReservationTransaction;
command.CommandText = "ReserveConnectionWithUser";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter() {
ParameterName = "user",
DbType = System.Data.DbType.String,
Direction = System.Data.ParameterDirection.Input,
Value = userName
});
command.ExecuteNonQuery();
}
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, ConnectionBase.Title);
ConnectionBase.CloseAndDisposeConnection(ref connection);
ok = false;
}
return ok;
}
Final Words
I’ve had an interesting time writing this article and I hope you’ll benefit from it. I know I haven’t included all possible variations. For example, one missing variation is to use program authenticated user with the database procedure but in a situation where access to system wide views is allowed. However, as explained in last chapter, the modification is very small, so I didn’t want to bloat the article.
Of course, if you have further ideas or questions, please feel free to leave a comment. But in any case, enjoy coding!
Links and References
History
- 13th October, 2016: Created
