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”

Loading Excel into DB on 64 bit machine

Loading Excel into DB was much easier but it became almost impossible in SQL 2008 (64 bit machine).
Here is the fix.

Fix perfectly works with “Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)” and “Office 2010”.

One time Activity

1.      1). Install “AccessDatabaseEngine_x64.exe” from here on server

2.      2). Execute following code on Server

USE [master]

GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

GO

Your Code

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;IMEX=1;Database=D:\Nisarg\Sample.xlsx','SELECT * FROM [Sheet1$]')