The Fix Required to use a Web Service that returns a dataset (SQLServer)

Get to the C:> on your machine (DOS shell)

Type the following line of code at the C: prompt.

osql -E -S (local) -Q "sp_grantlogin 'BLAB121\ASPNET'"

Note that the name of your SQL server should take the place of the “BLAB121” in the example (mouse over the SQL server icon in the sys tray to see the name of your SQL server.)

(example below is how it looks in the DOS Shell Environment)

Next you need to give the newly added ASPNET login in SQL server the proper permissions to manipulate the database.

Download this MSDE tool (MSDE Manager for .Net)
http://www.whitebearconsulting.com/Downloads.htm

Select the one that says .Net after it.

Install it where ever you wish then run the executable file.

When the program starts select file then connect

Leave the default settings and hit the connect button.

After it connects to the database select the “Set a login’s server roles”

Select the ASPNET login then select the “sysadmin” role then click the done button

Close the application.

Create a Web Service to connect to an SQL server database and an ASP.Net program to consume the service. It should work.

Contributed by Keith Gibb, Fall 2005