Supporting a SQL Server can be a challenging task, especially when issues arise at inconvenient times. In this article, we will explore how to use System Management Objects 2012 (SMO) to troubleshoot and resolve a security issue on an SQL Server.

Imagine it’s 3am and you receive a support pager notification. Despite not being on call, you reluctantly agree to cover for a colleague who is sick. Armed with a support laptop, you quickly realize that it lacks the necessary tools to diagnose and fix the SQL problem at hand. This is where SMO comes to the rescue.

SMO is a powerful programmatic interface that allows you to interact with the internals of a SQL Server. It can be used to automate manual tasks, integrate applications directly with the server, and retrieve data. While it may not replace SQL Server Management Studio (SSMS) for day-to-day tasks, it is invaluable in situations like this.

To get started with SMO, you will need to download and install the SMO MSI’s. Once installed, you can use Visual Studio 2010 (or later) to create a web application project. Make sure to add the necessary references to the project:

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoExtended

With the project set up, you can now begin troubleshooting the SQL Server issue. Start by establishing a connection to the server using specific credentials. Here’s an example of how to do it in C#:

Server myServer = new Server("MYSQLSERVER");
myServer.ConnectionContext.ConnectAsUser = true;
myServer.ConnectionContext.ConnectAsUserName = "peterban@MYDOMAIN";
myServer.ConnectionContext.ConnectAsUserPassword = "myPassword";
myServer.ConnectionContext.Connect();

If the connection is successful, you can proceed to retrieve a list of databases on the server:

myServer.Databases

Once you have access to the server, you can also examine the server logs. For example, you can retrieve the application log using the following code:

EventLog mySmoLog = new EventLog("Application", "MYSQLSERVER");

With the logs in hand, you can identify any authority issues or errors. In our scenario, we discovered an authority issue on the “MyInstantHelpdesk_Development” database. To further investigate, we retrieved a list of users associated with the database:

UserCollection myUsersList = myServer.Databases["MyInstantHelpdesk_Development"].Users;

From the list of users, we identified the problematic user and wanted to see which roles they belonged to. This can be achieved with the following code:

StringCollection myRoleList = myUsersList["myinstanthelpdesk"].EnumRoles();

Finally, to resolve the authority issue, we added the user to the “db_owner” role:

myUsersList["myinstanthelpdesk"].AddToRole("db_owner");

After making these changes, we observed that the errors stopped and the support pager ceased its incessant beeping.

While this article presented a contrived scenario, the programs discussed can serve as a starting point for documenting the security on a server or a group of servers. SMO provides a valuable toolset for troubleshooting and automating tasks on SQL Servers.

Remember, support is not always fun at 3am, but with the right tools and knowledge, you can overcome any SQL Server challenge that comes your way.