There are a multitude of administrative actions you can automate using SMO. You can read more about SMO in Books Online (http://msdn2.microsoft.com/en-us/library/ms162169.aspx). There is also a forum on MSDN devoted to SMO (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1).

I recently came across this code snippet for changing the service account password for the Relational Engine and Agent. You can do this manually using SQL Server Configuration Manager. But if you have to manage tens if not hundreds of servers/instances this can be a real pain; especially if you have to do it every 30, 45, or 60 days.

You can incorporate this code into a small application that prompts you for the old password, new password, and servers/instances. There are a bunch of enhancements that could be made to this code – error handling being one of them. The point here is you shouldn’t just copy this code and start using it verbatim.

BTW: The code doesn't come with any warranties express or implied!

ManagedComputer server = new ManagedComputer(srvName);

//note this will fail if the SQL Server is disabled or when SQL Agent with Agent XPs set to 0

foreach (Service service in server.Services)

{

if (service.Type == ManagedServiceType.SqlAgent || service.Type == ManagedServiceType.SqlServer)

                {

                    Console.WriteLine(service.Name);

                    Console.WriteLine("Service Account: {0}", service.ServiceAccount);

                    service.ChangePassword("oldpassword", "newpassword");

                }

}