Limit access in SQL Enterprise Manager?

7 Feb 2007 10:14 PM

Rich L

We have SQL 2000 server. And our DBA clients connect to the various
databases on their using SQL Enterprise Manager installed on each of their
computers. For authentication to the databases they can connect by either
Windows Authentication or SQL authentication inside of SQL Enterprise
Manager. We do want to keep this dual authentication capability. The
problem is the owner here also has SQL Enterprise Manager installed on his
computer. (He is not a DBA and that worries us). What I would like to do is
somehow edit his SQL Enterprise Manager so that it would only allow him to
connect via an SQL account and NOT by Windows Authentication. Is there a reg
hack or ini file that I can edit that would remove or grey out this option?
-Rich

7 Feb 2007 11:08 PM

Erland Sommarskog

Rich L (Ri***@discussions.microsoft.com) writes:

> We have SQL 2000 server. And our DBA clients connect to the various
> databases on their using SQL Enterprise Manager installed on each of
> their computers. For authentication to the databases they can connect
> by either Windows Authentication or SQL authentication inside of SQL
> Enterprise Manager. We do want to keep this dual authentication
> capability. The problem is the owner here also has SQL Enterprise
> Manager installed on his computer. (He is not a DBA and that worries
> us). What I would like to do is somehow edit his SQL Enterprise Manager
> so that it would only allow him to connect via an SQL account and NOT by
> Windows Authentication. Is there a reg hack or ini file that I can edit
> that would remove or grey out this option?

No, and that would be a meaningless thing to do. He could just as well
connect with Query Analyzer or OSQL. Or from another computer.

But you can of course use sp_revokelogin to prevent him from logging into
with his Windows user at all. But I don't really understand the point
with this. Generally, Windows authentication is beter than SQL
authentication. Why would require him to login with SQL authentication?

(And "the owner here". Sounds like someone who is paying your wages.
Probably a good person to be friend with. :-)

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx