Forward

This article is a light hearted look at how to use System Management Objects 2012 (SMO) to trouble shoot a support issue.  The scenario will look at a security issue to illustrate

• Logging into an SQL server using specific credentials
• Retrieving a list of databases
• Retrieving the users for a specific database
• Retrieving the roles for a specific user of a database
• Adding a role to a specific user of a database

Provided with this article is a VS2010 project to help you run the code discussed.  If you have not installed the Client SDK, you will need to download the SMO MSI’s use the following links:

• X86 - http://go.microsoft.com/fwlink/?LinkID=239658&clcid=0x409
• X64 - http://go.microsoft.com/fwlink/?LinkID=239659&clcid=0x409

Support is not fun at 3am

“Its 3am” I groaned, looking at the clock.  “What is that annoying bleep?  Oh, the support pager!” I thought, “But I’m not on call tonight”. 

Then it came flooding back to me.  Just as I was leaving work, Paul the IT manager, came over and asked me to cover the pager as the person on call had phoned in sick.  Begrudgingly, I had agreed. I picked up the support laptop from the tray and headed out for my weekend away. 

“Thanks!  Without you, we wouldn’t have cover tonight! I will make sure that you get some more time off for this. Trust me, there won’t be any problems” he shouted after me as I left. 

 “Bleep, Bleep, Bleep”, went the pager again as another message came through.  Rubbing my eyes to get rid of the sleep I shuffled over to look at the cursed thing.  Unnervingly, the pager said “25 unread messages”.  

“BLEEP, BLEEP, BLEEP, 26 unread messages”.  “Oh ****” I thought, “This is serious.  I better get the laptop up and running”.

I turned on the hotel kettle while the laptop booted up thinking “Coffee needed pronto!!”

The support laptop had definitely seen better days, but through the clearing sleep fog I remembered that this had been recently been rebuilt by the person who was off sick.  “I hope he added back all the support tools,” I thought as my coffee started to work its wonders.

Logging in, I did a quick inventory of the laptop and only found a VPN client and VS2010, no SQL2012 (our version of SQL), “Oh, Oh,  how can I find out, let alone fix the problem,” I tought as I lookrf at the cryptic message on the pager “Call support there is a SQL problem”.  “At another time that would be funny” I thought wryly as I drank the last of the super strength coffee. 

Since there was only limited access to the SQL server from the tools I had on the PC I decided to use SMO to do the trouble shooting.  A quick download from the links in Figure 1 and an install later, I had SMO installed on the laptop. I fired up VS2010 and created a web application project, my preferred project type. I added the following references to the project so I could start using SMO:

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

“Great!” I thought, “A quick test to make sure I can use SMO through the VPN”.  I added a web form to the project called Program1.aspx (see attached VS2010 project) and added the namespace for SMO to the C# code.

using Microsoft.SqlServer.Management.Smo; 

and then inserted the following code for the connection:

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

“FAILED! What do you mean failed!”, I thought as the connection failed miserably.  “Hmmmmm, I remember now”, the ConnectAsUserName part of the connection doesn’t work as a domain name, it works like the SSRS configuration email setting. 

Hence,  I changed the ConnectAsUserName  from MYDOMAIN\peterban to be peterban@MYDOMAIN and so make my connection code :

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

“Cool, it connected.  Let’s just put a list of databases in a GridView to check things”, I thought as I remembered that the code snippet to get the list of databases from the server object was:

myServer.Databases

Program1.aspx shows the code to put a list of databases on the screen similar to the following list:

Since I had the connection working, I could now start to look at the logs.  To do this, I added a new web form to the project Program2.aspx (see attached VS2010 project) copied my base connection from Figure 2 into the code behind and then and linked a GridView to the output of the EventLog.

While not related to SMO, once the connection is made, the Windows logs can be accessed using the following line of code:

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

Where “Application” is the type of  server log you are trying to access and “MYSQLSERVER” is the SQL Server name.  The program Program2.aspx contains the full listing for this, with a sample of the application log retrieved shown below:

Figure 3

The sample of the log shown in Figure 3 highlighted that that some of the SSIS jobs were failing.  Looking at the detail it was clear that there was an authority issue on one of our databases “MyInstantHelpdesk_Development”.  A closer look at the screen shot showed the following:

Figure 4

From Figures 3 and 4, I knew which database was the problem and that there was an authority issue with the user running the job. Hence, the next step was to retrieve a list of the users on the database to see who was not able to execute the stored procedure.

To access the user collection associated with the database I added another web form Program3.aspx to the project (see attached VS2010 project), copied in the connection information from Program1.aspx, and added the following code snippet to get the user collection associated with the database:

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

Program3.aspx puts the full list of users for this database on the screen as the following image shows:

Figure 5

Gulping my second super strength coffee I looked at the GridView output in Figure 5 and remembered that the login used for all of our internal systems was the application name, so the problem was the user “myInstantHelpdesk”. 

Quickly, I added web form Program4.aspx to the project (see attached VS2010 project) and added code to show me the list of roles that this user belonged too.  The key elements to get to the roles being the following code snippets:

// Gets the list of users for the database
 UserCollection myUsersList = 
         myServer.Databases["MyInstantHelpdesk_Development"].Users;
// gets the roles for a specific user
 StringCollection myRoleList = 
         myUsersList["myinstanthelpdesk"].EnumRoles();

Running the program gave me the list of roles shown below: 

”Ah HA – got it!” I thought. “Just adding an ownership role will give full access to the database.  Once I do that the job should be able to execute a stored procedure and I can go back to sleep”.  

As I added web form Program5.aspx to the project (see attached VS2010 project) I mused on how much time off I should demand from Paul. The key code snippet from Program5.aspx is the following code:

UserCollection myUsersList = 
         myServer.Databases["MyInstantHelpdesk_Development"].Users;
Microsoft.SqlServer.Management.Smo.User myUser = 
         myUsersList["myinstanthelpdesk"];
myUser.AddToRole("db_owner"); // Add db_Owner role to the current SMO user

A quick check of the logs showed that there were no more errors being logged and the beeping of the pager stopped.  Phew!

  “Trust me, there won’t be any problems,” sounds like a couple of days, I chuckled as I saved the project onto my memory stick.

Conclusion

System Management Objects provide a powerful programmatic interface to the internals of a SQL server. 

While it is unlikely that SMO will replace SSMS as a tool of choice for the day to day tasks on an SQL server, it certainly has a place when manual tasks need to be automated, or applications need to integrate directly with the server and not just retrieve data.

While this article has been a little contrived to highlight certain aspects of SMO, the programs could form the basis of documenting the security on a server or a group of servers.