How To – Grant Access to Builtin Administrator to SQL Express Instance

Griffon

Hello World,

If you have read my post about installing SQL Express 2008 , you might have noticed the important note about adding the Builtin\administrators group to the SQL server Admin role during the setup.  As explained, a lot of people simply add the user account used to perform the installation into this role.  Problem arise when another person tries to access SQL and get an access denied error message. To solve this problem, you have to options

  • Option 1 – Re-install the SQL 2008 Express and select the correct parameters
  • Option 2 – Using the command line in order to add the requested group to the SQL Server Role and grant this group access to the Database

Let’s go for option 2

Using the Command line to grant Access to the Administrators Group

We assume that you can login with the account that has performed the installation and that has indeed access to the SQL Database

From a command prompt, type the following

………………………………………………………………………………………………………………………………………………

osql  -S  <%ServerName%\%InstanceName%> – E 

Create Login [Builtin\Administrators] From Windows 

go 

exec sp_addsrvrolemember ‘Builtin\Administrators’,’sysadmin’

go

…………………………………………………………………………………………………………………………………………………………..

Type Exit and close your Command prompt

After this operation, other users will be able to access the SQL instance and perform their job.  As you can see not really complex.  I’m sure that next time you will perform the SQL 2008 Install you will remember to pick up the Builtin\administrators group.

Till next time

see ya

Leave a Reply