How do I restrict access to Enterprise Manager

[WinniePooh]

Is there any possible way that I can restrict access to Enterprise Manager? Currently anyone who has access to our server is able to open Enterprise Manager which is obviously not advisable! Do you know how I can password protect Enterprise Manager thus prompting, for example, for the sa password to be entered before opening it?

Any advice would be appreciated!

Many thanks

 

[Corran007]

How exactly do you have security set up?

do you have windows or mixed? Unless everyone is using the same login to the server, they should not have access unless you have given it to them.

 

[WinniePooh]

Our Windows 2000 server where SQL server is installed is permanently locked and only IT staff have access to unlock it/know the password. However, we are trying to restrict access to our databases by only allowing the DBA team to run up Enterprise Manager. I'm therefore hoping to find a way of changing the startup of Enterprise Manager to prompt for a password.

 

[Corran007]

easy enough to fix, but it has some restrictions

first you cannot use windows authincation for that user. you should to create a specific sql server user for that machine.

create your new user, give it sysadmin privilages.

then go to em and right click the server and select edit sqlserver registration. Click use sql authinication if windows is checked. if its already sql, change it to the new username/password. click the checkbox that says always prompt for loginname/password. that should fix it.

you may be temped to use sa, dont. you should never use sa for anything unless you have no other option. like a vendor supidly codes it so that sa user will only work for an application. If you already have a generic user with sysadmin privilages (which you shouldnt, but who knows) you can just use that instead of making a new one.