April 13, 2006 at 9:50 am
Is there a way in SQL2000 to hide DTS/Security/Databases/Management, etc.., from a SQL user account in EM ? I only want them to have access to one database, that's it. period.
I don't think it can be done with 2000, but I thought I'd ask. What about 2005 ?
April 13, 2006 at 10:50 am
No those are items of the MMC snap-in itself and cannot be removed.
April 13, 2006 at 11:39 am
Ya that is what I though. Thank you for your quick reply.
April 14, 2006 at 4:36 am
But if you have DTS packages you can hide them from the user, just remove the permission from the correct system SPs....
April 17, 2006 at 8:30 am
Can you elaborate on this? I'm not sure exactly what you mean. I have the sql user only having access to one database. ReadOnly.
April 17, 2006 at 8:39 am
The Guest user has access to read tables in msdb, where the DTS packages are stored. You can remove the permissions to the stored procedures used to scan for the packages. A profiler trace while you open the node in EM will get you the ones being called.
April 17, 2006 at 9:12 am
But it's not a 'Guest' Account. There is a SQL account created that they are using, which only has access to one database. The rest are unchecked. Does that mean they still have read access to msdb ?
April 17, 2006 at 9:24 am
Everybody inherits rights via the guest account. Guest has been explicitly assigned access to the msdb database and made a member of the Public role which is why they have access to scan the DTS packages stored in msdb. Now to prevent just access to the DTS packages as far as seeing and creating you should create another role called something like No_DTS and explcitily deny access to the Stored Procedures invoked during DTS events such as save a new package and scanning the existing packages. Then you assign guest to this new role and because of most restrictive rule they can no longer perform those tasks. This does not remove the DTS subtree in EM can just prevents seeing, opening and saving packages on the server. They can still create new packages but can only save a structured storage files or VIsual Basic files.
April 17, 2006 at 12:29 pm
That makes more sense now. I didn't know new users inherits all the Guest rights. I created a role and denied a few stored procedures and now the user can't see or save any DTS packages. Thanks for all the help.
April 17, 2006 at 1:05 pm
When a login doesn't map into a database by any other context (user), if guest is enabled for the database, the user gets the rights assigned to guest. The databases where guest is enabled are: master, msdb, tempdb. The master and tempdb databases must maintain access by guest. You can remove the guest user database access for msdb, usually with no ill effects.
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply