HOWTO Connect to MSDE from SQL Enterprise Manager

[Soul]

First

You have to enable the named connection for the MSDE service.

Run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe

Select tcp/ip and move to enabled protocol

Second

Run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql -E

This will give you a trusted sql server connection where you can issue commands

Third

Type the following commands...

1> use master
2> EXEC sp_addlogin 'yourusernameyouwanttobeadmin','yourpasswd'
3> EXEC sp_addsrvrolemember 'yourusernameyouwanttobeadmin', 'sysadmin'
4> go

This should create a user with the name 'yourusernameyouwanttobeadmin' and with 'yourpassword' that you can use to register your SQL Server into SQL Enterprise Manager.

[charles]

Thanks!

I should have let you into the BETA sooner ;)

charles

[Soul]

Well had I had problems with my colocated machine at some other isp I would have probably found defender hosting (which I will likely move my colocated machine to in the near future) sooner and then I would have relocated my linux vds hosting from even a different isp that I was unhappy with sooner and then I would have noticed the windows beta VDS sooner :D

[lmnop]

Hello,

What commands should be used if I want to let my client to connect to MSDE from SQL Enterprise Manager?
What role I should set?
And how I could revoke his access or change it?

[Soul]

You would probably set them to database owner. You can use sql enterprise manager for that. You merely need to allow sql enterprise manager by using the above steps in the first post of this thread.

Once you have done that then you can add a new sql user and give them a username and password and make them owner of the database. Then with the ip address of the machine they can register their connection to the sql server from their local sql enterprise manager. They need to already have sql enterprise manager though. You can't give the software to them as you have to have a license for it.