Is it possible to prevent EM connections?

Question

Post reply

Is it possible to prevent EM connections?

RichB

SSCrazy Eights

Points: 9651
More actions
February 17, 2006 at 9:57 am

#152773

Too many people use EM to #look# at the production database.
Without gettting into a debate about security, is there any way to prevent Enterprise manager being able to connect without affecting anything else?

Cheers.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

Don't install it on "too many people"'s machines.

A.J.
DBA with an attitude

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 2

select spid from master..sysprocesses where program_name = 'MS SQLEM'

kill those spids.

I'll warn you before though, if you get spids 50,51,52 from the above query, there is no guarantee that 3 seconds later those spids will still be held by those EM processes.

Perry-300990 Default port Points: 1451 More actions · Answer 3

This

"...Without gettting into a debate about security, is there any way to prevent..."

struck me as a somewhat entertaining juxtaposition of desires -- almost as a desire to talk about how to secure something without talking about security

RichB SSCrazy Eights Points: 9651 More actions · Answer 4

Indeed. Imposing the security I want is going to be a big job. I have inherited a system that is somewhat out of (firm) control. You know the ones - 20 odd developers writing inline sql, all running under administrator privs. GOK whats going on.

Security is being addressed, but as I said that is not what I wish to discuss here. My problem is with the way folk casually pop open a proc or table in EM - I fear this tool, especially in the hands of web monkeys... hence the question.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I don't think you can prevent the connections, but you could secure the production systems so that the developers do not have alter/create rights for database objects. This would mean you would have to provide development systems in which they could work, but that is how it should be anyway.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

You should be able to remove all of the registered servers from the client machines by editing the registry on each machine. Then you can set permissions on the appropriate keys, preventing them from re-registering the servers. I suppose that a group policy object could be used to accomplish some of this, but perhaps not. I'm not sure.

EM uses the following area to store registrations (on my computer, anyway):

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X so I would imagine that you could add a properly-crafted "deny" on that key to prevent new registrations. I don't have time to try it, but it just might work.

Another option would be to hide behind them and beat them with a sturdy object every time they do something that is not allowed. Good luck with this and let us know how it turns out.

hth jg

Ton de Vries Newbie Points: 3 More actions · Answer 7

I have the same problem with too many people are using EM.

I now hide behind them and beat them with a sturdy object every time they do something that is not allowed.

But Wednesday is my free day. I want to know who I have to beat on Thursday.

Is there a possibility to log the using of EM

Thanks

Ton

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 8

Hi

Here U need to check one thing are they all using the sa account to get connected to SQL server thru EM or what??If they are using the sa account then probably you need to change the sa password and create a separate loginid's for them with proper rights and then even if they connecte thru the em then no probs as they will not be able to do much harm since they wont have much rights..

devaraya Default port Points: 1455 More actions · Answer 9

Hi,

Is there any procedure runs each minute to prevent unauthorised users from accessing the EM by identifing their system name/I.P.Address? Any ideas?

Ramaa

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 10

You can sort of limit the usefulness of EM by making some changes on the databases :-

think carefully about this first , and test thoroughly and document 100% .

Assuming all your users are not dbo's or sysadmins .. remove public permissions from syscomments, sysusers, sysdatabases etc. etc. Taking out the public rights adds a certain level of security - syscomments is very good - stops users looking at procs etc.

You should restrict public rights as part of a security policy.

more info at http://www.sqlsecurity.com/

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/