Wednesday, February 9, 2011

Connect Local SQL Server when you are not part of "Sysadmin"

There might be a situation when one is part of “Administrators” on local system but not part of “sysadmin” on local SQL Server and hence he is not able to connect to local SQL Server.

This situation can be handled and fixed by following below mentioned steps.

1.       Go to All Programs
à SQL Server 2008
à Configuration Tools
àSQL Server Configuration Manager
2.       Go to “SQL Server Services”
3.       Right click on “SQL Server (MSSQLSERVER)” and click on Properties
4.       Click on “Advance” tab
5.       Add “-m;” in the beginning of value of “Startup Parameters”. So it should look something like this.
-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
6.       Click “OK”.
7.       Go to All Programs à SQL Server 2008 à (Right click) SQL Server Management Studio à Run as Administrator
8.       Now you should be able to connect
9.       Add yourself in “sysadmin” group
10.   Close SSMS
11.   Follow 1 to 4 steps
12.   Remove “-m;” from beginning from value of “Startup Parameters”.
13.   Click “OK”

No comments:

Post a Comment