Enterprise Manager question

Hi everyone,
Wondering if someone can help me with this scenario:
We need to give one of our clients access to their database which is
currently running on one of our SQL servers. We have created the VPN access.
We have also created a SQL login id and given that login-id dbo privileges t
o
the particular database. Once the client connects via the VPN, using SQL
enterprise manager, he can connect to the database that he needs. He can see
the other db's on the server but cannot go into the tables of those
databases. That is good. However the problem is that he can go to the other
categories in Enterprise manager such as DTS, Management etc. He can see the
users that are defined on the system. We do not want him going into any of
those options. Is there a way in which we can stop him from probing things o
n
our server. He needs to have access to only one DB and that's all.
Any help in this matter will be greatly appreciated.
Regards
RCdid you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:

> Hi everyone,
> Wondering if someone can help me with this scenario:
> We need to give one of our clients access to their database which is
> currently running on one of our SQL servers. We have created the VPN
> access. We have also created a SQL login id and given that login-id
> dbo privileges to the particular database. Once the client connects
> via the VPN, using SQL enterprise manager, he can connect to the
> database that he needs. He can see the other db's on the server but
> cannot go into the tables of those databases. That is good. However
> the problem is that he can go to the other categories in Enterprise
> manager such as DTS, Management etc. He can see the users that are
> defined on the system. We do not want him going into any of those
> options. Is there a way in which we can stop him from probing things
> on our server. He needs to have access to only one DB and that's all.
> Any help in this matter will be greatly appreciated.
> Regards
> RC
>
could you not just give him Query Analyzer? What do they need that
requires the full SEM?
AFAIK the only extension that can be pulled from the MMC is the MetaData
services (not much help for you).
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||How can we do this (forgive me if this question comes across as a stupid one
)
when the user is not in my AD domain? I tried configuring this user as a
Windows NT user and then allowing him access to the DB. But I cannot connect
to the server under those conditions as to use Windows authentication - I
assume - we need to be on the same domain.
"Neil MacMurchy" wrote:

> did you hear "examnotes" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
>
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||The client wants to run scripts and also import data into tables.
"Neil MacMurchy" wrote:

> did you hear "examnotes" <RC@.discussions.microsoft.com> say in
> news:2A35260C-7263-467E-B6F8-AF9A2D755E34@.microsoft.com:
>
> could you not just give him Query Analyzer? What do they need that
> requires the full SEM?
> AFAIK the only extension that can be pulled from the MMC is the MetaData
> services (not much help for you).
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||did you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:FDA1529D-F67F-40E5-97D6-F7D950D6AA25@.microsoft.com:

> How can we do this (forgive me if this question comes across as a
> stupid one) when the user is not in my AD domain? I tried configuring
> this user as a Windows NT user and then allowing him access to the DB.
> But I cannot connect to the server under those conditions as to use
> Windows authentication - I assume - we need to be on the same domain.
use a standard SQL login. You will need to have the server in Mixed mode
authentication but this is the only way to do this.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:EADF1A2E-01F0-4AD9-A900-E3EAD10C7618@.microsoft.com:

> The client wants to run scripts and also import data into tables.
script can be handled through QA. As for the Import/Export, you could
create the package for them once (assuming they will be running the same
import over and over again) and then save it as a structured storage file,
put the package on a flopy or ftp site and have the client use DTSRUN.EXE
to run the package. There is also BCP, creating a custom app in VS, or 3rd
party products like TOAD for SQL
(http://www.quest.com/toad_for_sql_server/index.asp) that might be of use.
(TOAD is free, but you have to re-download it every 60 days which is a
pain)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
news:Xns96CAA3729742Aneilmcsehotmailcom@.
207.46.248.16:

> 3rd
> party products like TOAD for SQL
> (http://www.quest.com/toad_for_sql_server/index.asp) that might be of
> use.
> (TOAD is free, but you have to re-download it every 60 days which is a
> pain)
DOH! TOAD's free version doesn't include an import. Sorry about that...
SQL Import from EMS is only $65 though
http://www.sqlmanager.net/en/products/mssql/dataimport
(they have a manager as well, but it also allows you to see the users)
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Neil, thanks for your tips so far. A quick question though.
My problem is that my client is fairly knowledgeable. If I create a standard
SQL login for him and assign him permissions only on that database, he can
easily figure out that if he can connect via Query Analyzer, he might be abl
e
to connect via SEM as well. I currently have assigned his SQL login dbo
permissions on that particular database.
"Neil MacMurchy" wrote:

> did you hear Neil MacMurchy <neilmcse@.nospamf.gmail.com> say in
> news:Xns96CAA3729742Aneilmcsehotmailcom@.
207.46.248.16:
>
> DOH! TOAD's free version doesn't include an import. Sorry about that...
> SQL Import from EMS is only $65 though
> http://www.sqlmanager.net/en/products/mssql/dataimport
> (they have a manager as well, but it also allows you to see the users)
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>|||did you hear "examnotes" <RC@.discussions.microsoft.com> say in
news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:

> My problem is that my client is fairly knowledgeable. If I create a
> standard SQL login for him and assign him permissions only on that
> database, he can easily figure out that if he can connect via Query
> Analyzer, he might be able to connect via SEM as well. I currently
> have assigned his SQL login dbo permissions on that particular
> database.
>
you will be in a bit of a bind then. There is much that would keep someone
from downloading the SEM (from an eval version or like) or some other tool.
There is the ability to use GPOs to keep people from using applications,
but since he is outside your domain this wont help.
You can lock down the access he gets (assign permissions to just the one
database is a start, though everyone will get access to Master and tempdb)
but you can look at denying permission on certain system wide functions
(create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you will
need to spend some time.
if it's going to be a real problem I would question giving them access at
all. Place them on a separate server and if the cuase thier own headaches,
they deserve them.
then again, perhaps someone else knows of a way...<g>
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hey Neil,
I took the help of one of my colleagues and he dug out this article:
http://groups.google.com/group/micr...6d78f72cf839622
I used step g from this article and was able to stop the user from viewing
the packages. Now the only issue I have to worry about is to stop him from
viewing the SQL logins on the server. He can see the logins but cannot do
anything with them. I would neverthless not like him seeing the users at all
if I can.
Thanks for all your help.
Regards
"Neil MacMurchy" wrote:

> did you hear "examnotes" <RC@.discussions.microsoft.com> say in
> news:B0BD1429-D580-4454-A286-3A028BE5164B@.microsoft.com:
>
> you will be in a bit of a bind then. There is much that would keep someone
> from downloading the SEM (from an eval version or like) or some other tool
.
> There is the ability to use GPOs to keep people from using applications,
> but since he is outside your domain this wont help.
> You can lock down the access he gets (assign permissions to just the one
> database is a start, though everyone will get access to Master and tempdb)
> but you can look at denying permission on certain system wide functions
> (create/alter/drop database, sp_grantlogin, sp_addlogin, etc.) but you wil
l
> need to spend some time.
> if it's going to be a real problem I would question giving them access at
> all. Place them on a separate server and if the cuase thier own headaches,
> they deserve them.
> then again, perhaps someone else knows of a way...<g>
> --
> Neil MacMurchy
> http://spaces.msn.com/members/neilmacmurchy
> http://spaces.msn.com/members/mctblogs
>
>