Any valid login can access Enterprise Manager

Hi.
When creating a SQL Server2000 login (NT Authen) with read-only rights to
user tables in a user database, this very same login can:
1. Login into EM
2. Though cannot change any objects, but can
- 1. view all system objects (logins, DTS etc)
3. STOP SQL Server Agent
4. RESTART SQL SERVER!!!!
This all seem to be traced back to the fact every login is a member of the
PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
Can anyone tell me how to:
1. Prevent user (not DBA, DBO's etc) login into EM?
2. Prevent user login into QA?
Cheers!> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
prevent access to msdb. This will prevent viewing DTS packages. See
http://support.microsoft.com/defaul...b;en-us;282463.
You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users from
enumerating logins via EM.

> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
The ability to stop and start services is controlled through Windows
permissions, not SQL Server security. If the account is a member of the
Windows 'Administrators' or 'Power Users' groups, then the user can stop and
start services using any tool or command. EM will not allow non-privileged
users to stop/start services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
> Hi.
> When creating a SQL Server2000 login (NT Authen) with read-only rights to
> user tables in a user database, this very same login can:
> 1. Login into EM
> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
> This all seem to be traced back to the fact every login is a member of the
> PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
> Can anyone tell me how to:
> 1. Prevent user (not DBA, DBO's etc) login into EM?
> 2. Prevent user login into QA?
> Cheers!|||Thks Dan - it sure works - but still no way of preventing a valid SQL Login
to access other objects on EM or seeing them using other tools (such as
Visual Studio).
Thks again!
"Dan Guzman" wrote:

> You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
> prevent access to msdb. This will prevent viewing DTS packages. See
> http://support.microsoft.com/defaul...b;en-us;282463.
> You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users fro
m
> enumerating logins via EM.
>
> The ability to stop and start services is controlled through Windows
> permissions, not SQL Server security. If the account is a member of the
> Windows 'Administrators' or 'Power Users' groups, then the user can stop a
nd
> start services using any tool or command. EM will not allow non-privilege
d
> users to stop/start services.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
>
>|||By default, SQL 2000 users can read catalog meta data in those databases
they have permissions to access. It's possible to revoke public permissions
from some of the catalog objects but this can break data access API's so
proceed at your own risk. SQL 2005 provides more control over meta data
access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...[vbcol=seagreen]
> Thks Dan - it sure works - but still no way of preventing a valid SQL
> Login
> to access other objects on EM or seeing them using other tools (such as
> Visual Studio).
> Thks again!
> "Dan Guzman" wrote:
>|||Thks Dan for all your help!
"Dan Guzman" wrote:

> By default, SQL 2000 users can read catalog meta data in those databases
> they have permissions to access. It's possible to revoke public permissio
ns
> from some of the catalog objects but this can break data access API's so
> proceed at your own risk. SQL 2005 provides more control over meta data
> access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...
>
>