2 January 2013

SQL Server 2008 R2 Local Database Instance


Setting up a SQL Server 2008 R2 Local Database Instance

Setting up and accessing a local database instance in SQL Server 2008 is a somewhat less then intuitive process.  This post will attempt to alleviate some of the wasted time and frustration of this process.  Please let me know if you experience any issues and also if you see anything that needs to be corrected.  Thanks and I hope this helps!

Start up the Local Server

  • Click Start -> Microsoft SQL Server 2008 R2 -> SQL Server Configuration Manager
  • Set the SQL Server(SQLEXPRESS) and SQL Server Browser to automatic start mode.
  • Right click -> Properties -> Service Tab
SQL Server 2008 Server Settings
SQL Server 2008 Server Settings

Login to Local Server

  • Now open up SQL Server Management Studio and Connect to Object Explorer and select Server Name: [Your PC name]\SQLEXPRESS
  • Example:  8540P-KL\SQLEXPRESS
  • To find your PC name:  Right click My Computer -> Properties -> Computer Name tab
  • Login using windows authentication:  Using the user name [Your Domain]/[Your User Name]
SQL Server 2008 Login Settings
SQL Server 2008 Login Settings



Setup User Account

  • In SQL Mgmt Studio -> Expand your local Server -> Security -> Right click on Logins -> New Login
  • Uncheck Enforce password policy, password expiration and user must change pw(Since this is local)
  • Default database -> Your Database
  • User Mapping Page -> Map to your db and grant db_owner role
  • Status Page -> Grant Permission to connect and Enable Login
SQL Server 2008 User Settings Local DB
SQL Server 2008 User Settings Local DB




Setup Access Permissions/Settings for User

  • Right click your Local Server -> Properties -> Security Tab -> Enable SQL Server and Windows Authentication Mode
  • Open SQL Server Configuration Manager -> SQL Server Network Configuration -> Protocols for SQLEXPRESS -> Enable TCP/IP
SQL Server 2008 Server Permissions