SQL 2008 and Default Administrative Rights

Author by Nathan Lasnoski

Have you worked with SQL 2008?  If so, then you've likely noticed the changes associated with default SQL administrative rights.  Most notably, the local server administrators group is no longer in the SQL administrators group.  You need to remember to set this when you initially deploy SQL 2008 on a server. If you've already deployed SQL, you don't need to completely re-deploy to fix the administrative rights.
  1. Stop all SQL services on the SQL server
  2. Launch the command prompt as an administrator (UAC)
  3. Navigate to the SQL bin directory
  4. Execute "sqlserv -m" => SQL server will start in single instance mode
  5. Open SQL Management Studio as an administrator (UAC)
  6. Add the necessary SQL administrator
  7. Close SQL Management Studio and close the command prompt (will stop SQL single instance mode)
  8. Start the SQL services
Here's the TechNet reference: http://technet.microsoft.com/en-us/library/dd207004.aspx I've found you need to check your SQL SPNs after the starting of SQL in single instance mode.  Make sure to validate those if you received authentication issues with Kerberos applications. Also, I've run into the error: "Token-based server access validation failed with an infrastructure error", which was solved by running SQL Management Studio as an administrator (UAC). Cheers! Nathan Lasnoski
Author

Nathan Lasnoski

Chief Technology Officer