Q In SQL Server™ 2000, I used to set the service account for the SQL Server Engine and Agent using the Services applet in Administrative Tools. Now I hear that in SQL Server 2005 I'm supposed to use the Configuration Manager tool. Why can't I just keep using the Windows tools?
A SQL Server 2005 is built to be more secure than previous versions. In many shops, users would just set internal accounts, such as LocalSystem, to run SQL Server. But these accounts often either have more or fewer rights and permissions under Windows® than they need. You should create a Windows account with no elevated privileges to run the SQL Server 2005 Engine and Agent services. If you select these accounts with the Configuration Manager, they will automatically be granted the proper rights and permissions in both SQL Server and the operating system. If you use the Windows tools to manage the SQL Server services, you might not grant the proper rights, or you may grant too many.
For more details, see the tip Changing the Service Account.
Who's Logging onto My Server?
Q I want to know who is logging onto my server and when. I also want to restrict some specific users to certain time periods and would like to know how to fire a trace to track down user activity. Is any of this possible?
A Yes, you can do all of these things with SQL Server 2005 if you have Service Pack 2 installed.
SQL Server 2005 allows you to create login triggers that can fire a T-SQL or stored procedure in response to a LOGON event. You can use a login trigger to audit and control users by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for specific logins. Note that the event is fired only after a login is successfully authenticated, but just before the user session is actually established. Therefore, all messages originating from inside the trigger (such as messages or errors) from the PRINT statement are sent to the SQL Server error log. If the authentication happens to fail for a login, then the Logon triggers are not fired.
The following example shows how you can create a login trigger and send a message to the SQL Server error log as soon as any user logs in: