Saturday, March 8, 2008

The importance of an account's SID

I keep highlighting to developers that an account's SID is more important than the other attributes. This is something that any IT professional or developer or DBA should take seriously. Take for instance a Windows account that was created for a user. The user manages to log in to a workstation and creates his or her documents and saves it in a personal folder. Accidentally, the account got deleted. Your approach will be to recreate the account so that the user can log back in. Logging back in is one thing but accessing the user's documents is another. You see, even if they assign the same account and password, the system sees a different SID. The user will not be able to access the documents unless an administrator takes ownership of the documents and assign the user as the new owner. Another application of this concept is in databases. Imagine trying to backup a database and restoring it on a different server. You may be able to restore the database but the users assigned to access the database won't be able to do so. Again, it's an SID issue. Which is why part of database disaster recovery plan is to include these accounts together with their SIDs so that you won't be having difficulty restoring and accessing those databases when needed

Cleaing up your Maintenance Plans in SQL Server 2005

Being a lazy guy that I always am, I try to find ways to make my life easier. When we finished fixing all the issues in our test server, I recommended that we simply backup the MSDB database and restore it on the production server. This way, I don't have to recreate the jobs on the production server. Well, that's just what hapened. I didn't have to recreate the jobs. Unfortunately, I had a database maintenance plan which I created in the test server and that got in my production server as well. I needed to either modify the maintenance plan to point to the correct server - which is my production server - or delete it and recreate another. I tried the easiest path - deleting the maintenance plan. When I did that, it was throwing me an error similar to the one below

TITLE: Microsoft SQL Server Management Studio
Exception has been thrown by the target of an invocation. (mscorlib)
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

I checked the maintenance plan and verified the connections and saw that it was the instance name of my test server. I checked the SQL Server logs on my test server and noticed that my production server's SQL Server service account was trying to connect. Good thing I applied best practice here or I would end up deleting the database maintenance plan on the test server from the production server. So, how do I do it? The workaround is to check the dbo.sysmaintplan_plans table of the MSDB database. The ID column can be used as a value for us to delete records in the dbo.sysmaintplan_log, dbo.sysmaintplan_subplans and dbo.sysmaintplan_plans tables. Once those records have been deleted, you can refresh your Maintenance Plans folder in SQL Server Management Studio and notice the maintenance plans disappear. This does not delete the jobs created by these maintenance plans. You still have to manually delete those jobs.