October 13, 2005 at 9:34 am
This topic should be Limit access with EM.
I have a remote developer that uses EM or some other front end app to connect to our DB. I have created a login for him and a Database Role that has only select access to a few tables. I tested his login with my EM by chaning the registration credentials to his. When I connected to the server I was able to view all the DB's and any table I wanted. But when I log onto QA with his credentials I can only access the few tables I allowed. How can I limit what he can see and access when our server is connected to via EM?
October 13, 2005 at 10:14 am
Ok I tooled around a little bit (deleted and recreated the account, then tried connecting on a different puter from the one I usually work from) and now this user is limited to what he can do (although he sees all he can't access all). I did however notice that he can access Master DB tables. Are these publicly accessible tables? Why would he have access to anything in the Master DB when I have not specified any permissions to that DB?
October 17, 2005 at 4:49 pm
The "guest" user account is (by default and by necessity) enabled in the master database, and (again by immutable default) is granted membership in the public role... and that role has loads o' access rights to the dangerous toys in the master database.
This is detailed in gory detail in a whitepaper Brian Kelley has posted (listed?) on the SANS website. Look in http://www.sans.org/rr/whitepapers/application/ for "SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role" for relevant info.
Philip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply