InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:46:17 GMT20http://www.sqlservercentral.com/Forums/Topic1405719-391-1.aspxUsing a domain account that is in the local Administrators group in Windows 2008 or above DOES NOT mean you get the same privilege set that you get when running under NT Authority/System.Windows 2008 and above have User Account Control, and one aspect of this is that accounts in the local Administrators group get two sets of privileges. By default they use the local User set of privileges. They only get to use the full Administrator set when the application is 'Run as Administrator'. There is in fact no need whatsoever for the account running the SQL Server services to be a member of the local Administrators group. What is needed is for the account to be explicitly given the privileges that it needs to run SQL Server efficiently.The Microsoft documentation has a ilst of the rights needed in various circumstances, but my consolidated ist would grant the SQL service account the following rights. Some of these are set up by the SQL install or SQL Configuration Manager, but others you need to grant explicitly. When you have done this, the domain account should perform at least as well as the Local System account.seAssignPrimaryTokenPrivilegeseBatchLogonRightseCreateGlobalPrivilegeseImpersonatePrivilegeseIncreaseQuotaPrivilegeseLockMemoryPrivilegeseManageVolumePrivilegeseProfileSingleProcessPrivilegeseServiceLogonRightseSystemProfilePrivilegeseTcbPrivilegeMon, 14 Jan 2013 03:30:50 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic1405719-391-1.aspxThanks - I ran that script for both different service login accounts.Both times the script gave identical results:auth_scheme SQLlogin_Name saBizarrely too, I also did a SELECT * FROM MyTable when logged in under the domain account, and this time it was fast, that is - the expected time. Must've been something playing silly buggers on my PC.I'll keep switching between the two over the next few days and see what happens.Sun, 13 Jan 2013 16:07:40 GMTDansterhttp://www.sqlservercentral.com/Forums/Topic1405719-391-1.aspxI can think of a couple of things to try.First go into SQL Server Configuration Manager and verify that shared memory is ahead of TCP/IP for the instance on your local machine. If not then you are using TCP/IP for connections to SQL, even if that connection is local and will be slower than shared memory in returning results.Another thing to check is how SQL is authenticating you.Is this instance of SQL on a domain or a workgroup? I wonder if the difference in performance you are seeing is due to the difference between Kerberos and NTLM authentication. Set SQL to use local system and then run this query:[code="sql"]select auth_scheme, login_name from sys.dm_exec_connections c inner join sys.dm_exec_sessions s on c.session_id = s.session_id where c.session_id = @@spid order by login_name[/code]Note the authentication scheme used. If you are connecting to SQL over TCP it should list Kerberos. Now change SQL to use a local system account, restart and run the query again. If it says NTLM then I wonder if your performance difference is due to NTLM vs Kerberos authentication.Fri, 11 Jan 2013 04:11:33 GMTJoie Andrewhttp://www.sqlservercentral.com/Forums/Topic1405719-391-1.aspxThat is not behavior I've run into before. Is there something special about your domain setup? I use domain specific logins for all my installations and haven't seen this type of behavior ever.Fri, 11 Jan 2013 04:08:05 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1405719-391-1.aspxI am an ERP software specialist.I have installed SQL Server 2008 R2 at several sites and generally have MSSQLSERVER service logging on with the Local System Account.That is how I have it on my own PC too.I have been fiddling with my own installation of SQL Server and have found that when I change the service login account from Local System Account to an account with admin rights on my local PC, then SQL Server runs really slow, like REALLY slow!For example, when SQL is running under Local System Account & I run a querySELECT * FROM MyTableThen the results are displayed instantly.Change SQL service to run with a domain account (or an account set up on my local PC with admin privileges) then the same query runs so long I have not bothered to wait to see how long it takes - I have waited about 2 minutes max. It should be instant! The table has maybe 200 records.Ideas?Thu, 10 Jan 2013 19:02:47 GMTDanster