When you run NUnit/C# unit tests against your local
instance, you are a dbo. As such, you are not getting any errors caused by
missing permissions. However, you can impersonate another user, and run your
unit tests in the context of that other user. The following test harness demonstrates
the technique:
using
System.Data;
using
System.Data.SqlClient;
using
NUnit.Framework;
namespace MyDB.Tests
{
[TestFixture]
public class ImpersonatorTests
{
private SqlConnection connection = null;
private Impersonator realUser;
#region constants
private const string userName =
"me";
private const string domainName
= "myDomain";
#region hide me
private const string pwd = "myPassword";
#endregion
#endregion
[TestFixtureSetUp]
public void
TestFixtureSetUp()
{
realUser = new Impersonator(userName, domainName, pwd);
connection = new SqlConnection("server=(local);trusted_connection=true;database=FinancialDW;");
connection.Open();
}
[TestFixtureTearDown]
public void
TestFixtureTearDown()
{
if (realUser
!= null) realUser.Dispose();
}
[Test]
public void
ImpersonationSucceededTest()
{
SqlCommand reader =
connection.CreateCommand();
reader.CommandText = "SELECT SUSER_NAME()";
reader.CommandType = CommandType.Text;
string suserName
= reader.ExecuteScalar().ToString();
Assert.AreEqual(@"myDomain\me", suserName);
//Console.WriteLine(suserName);
}
[Test]
public void
CanSelectFromSalesTest()
{
SqlCommand reader =
connection.CreateCommand();
reader.CommandText = "SELECT COUNT(*) FROM Data.Sales";
reader.CommandType = CommandType.Text;
int numRows = int.Parse(reader.ExecuteScalar().ToString());
Assert.IsTrue(numRows
>= 0);
}
}
}
So, if the myDomain\me user does not have the SELECT
permission on Data.Sales, I am getting an exception:
System.Data.SqlClient.SqlException: SELECT permission denied on object 'Sales', database 'MyDb', schema 'data'
The code that I used to implement the Impersonator class is
mostly copied and pasted from http://support.microsoft.com/?scid=306158
, and here it is:
using System;
using
System.Runtime.InteropServices;
using
System.Security.Principal;
namespace MyDB.Tests
{
public class Impersonator : IDisposable
{
/* code taken from
http://support.microsoft.com/?scid=306158
*/
public const int
LOGON32_LOGON_INTERACTIVE = 2;
public const int
LOGON32_PROVIDER_DEFAULT = 0;
WindowsImpersonationContext
impersonationContext;
[DllImport("advapi32.dll")]
public static extern int
LogonUserA(String lpszUserName,
String
lpszDomain,
String
lpszPassword,
int
dwLogonType,
int
dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
public static extern int
DuplicateToken(IntPtr hToken,
int
impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
public static extern bool
RevertToSelf();
[DllImport("kernel32.dll", CharSet = CharSet.Auto)]
public static extern bool
CloseHandle(IntPtr handle);
public
Impersonator(string user, string domain, string password)
{
if(!ImpersonateValidUser(user,
domain, password))
{
throw new Exception(string.Format(@"Logon
failed for {0}\{1}", domain, user));
}
}
private bool
ImpersonateValidUser(String userName, String domain, String password)
{
WindowsIdentity
tempWindowsIdentity;
IntPtr token = IntPtr.Zero;
IntPtr
tokenDuplicate = IntPtr.Zero;
if (RevertToSelf())
{
if
(LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) !=
0)
{
if
(DuplicateToken(token, 2, ref tokenDuplicate) != 0)
{
tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
impersonationContext =
tempWindowsIdentity.Impersonate();
if
(impersonationContext != null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if (token != IntPtr.Zero)
CloseHandle(token);
if
(tokenDuplicate != IntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}
#region
IDisposable Members
public void Dispose()
{
if
(impersonationContext != null)
impersonationContext.Undo();
}
#endregion
}
}