January 29, 2008 at 9:28 am
I have created a user account that has access to a specific database. When that user logs in via Enterprise Manager, they see the database, and 3 others (there are a couple dozen on the server). If I remove their access to the specific database and the user refreshes, that database goes away but the other 3 remain.
I have not assigned access to the other databases. Any ideas on how to remove these 3 tables from the user's access?
Thx.
January 30, 2008 at 5:12 pm
Are the other three databases master, msdb, and tempdb? The guest user is enabled by default in those system databases.
Greg
January 30, 2008 at 7:43 pm
Nope. They are other custom databases. The user is not in their security settings though.
January 31, 2008 at 6:00 am
Is it a domain login that is a member of a Windows group that has access to the 3 databases?
Greg
January 31, 2008 at 7:50 am
Hey Greg.
We are not on a domain structure. Novell here :sick:
-- Bryan
February 5, 2008 at 8:53 pm
I think that users can only see the other database in EM but when they try to open/expand those databases, it will just prompt him that he/she is not a valid user on that database. he/she can only open/expand the database where he has granted access.
:-):cool:
February 7, 2008 at 9:09 pm
If the Server in the enterprise manager in the users computer is registered using the user "sa" then he can access all the databases even restricting him since the server in his EM is registered with sys admin rights. You can unregister the server in his EM, register it again using the user account you've made (not sa) then he can only view the database were his username has access to.
"-=Still Learning=-"
Lester Policarpio
July 4, 2008 at 4:41 am
If you're just trying to limit the number of databases that a user sees to those that they actually have access to, you might want to consider installing this updated version of the sp_MSdbuseraccess stored procedure :
http://support.microsoft.com/kb/889696/en-us
Essentially, when EM populates the list of databases it goes through sysdatabases and pulls out each database name it finds. With the modified version it also adds a check to see if the user has access to each database, and only displays those which return as true.
Note that this only works through Enterprise Manager, a user connecting from 2000 Query Manager or 2005 Management Studio will still see all the db's since they use different methods to get the database list, so doesn't ensure users cannot see which other databases are in place, but does at least reduce the number of non-relevant databases they are shown.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply