Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Limit Access to Databases in Enterprise Manager Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 29, 2010 8:41 AM
Points: 3, Visits: 32
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.
Post #448952
Posted Wednesday, January 30, 2008 5:12 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:17 AM
Points: 4,065, Visits: 5,316
Are the other three databases master, msdb, and tempdb? The guest user is enabled by default in those system databases.

Greg
Post #449754
Posted Wednesday, January 30, 2008 7:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 29, 2010 8:41 AM
Points: 3, Visits: 32
Nope. They are other custom databases. The user is not in their security settings though.
Post #449777
Posted Thursday, January 31, 2008 6:00 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:17 AM
Points: 4,065, Visits: 5,316
Is it a domain login that is a member of a Windows group that has access to the 3 databases?

Greg
Post #449887
Posted Thursday, January 31, 2008 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 29, 2010 8:41 AM
Points: 3, Visits: 32
Hey Greg.

We are not on a domain structure. Novell here

-- Bryan
Post #449949
Posted Tuesday, February 5, 2008 8:53 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:12 PM
Points: 731, Visits: 432
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.

Post #451984
Posted Thursday, February 7, 2008 9:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 2:38 AM
Points: 398, Visits: 2,423
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

Post #453056
Posted Friday, July 4, 2008 4:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 29, 2013 1:44 PM
Points: 144, Visits: 426
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.
Post #528603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse