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”

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]

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

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

Your Code

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