Limit Access to Databases in Enterprise Manager

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Security » Limit Access to Databases in Enterprise...

Limit Access to Databases in Enterprise Manager

Rate Topic

Display Mode

Topic Options

Author

Message

Nomis-332058

Posted Tuesday, January 29, 2008 9:28 AM

Forum 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

Greg Charles

Posted Wednesday, January 30, 2008 5:12 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 4,010, Visits: 4,894

Are the other three databases master, msdb, and tempdb? The guest user is enabled by default in those system databases.

Greg

Post #449754

Nomis-332058

Posted Wednesday, January 30, 2008 7:43 PM

Forum 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

Greg Charles

Posted Thursday, January 31, 2008 6:00 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 4,010, Visits: 4,894

Is it a domain login that is a member of a Windows group that has access to the 3 databases?

Greg

Post #449887

Nomis-332058

Posted Thursday, January 31, 2008 7:50 AM

Forum 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 Sick

-- Bryan

Post #449949

Bryansky

Posted Tuesday, February 05, 2008 8:53 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:34 PM
Points: 731, Visits: 422

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.

Smile

Post #451984

Lester Policarpio

Posted Thursday, February 07, 2008 9:09 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 9:36 PM
Points: 398, Visits: 2,407

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

Keith Langmead

Posted Friday, July 04, 2008 4:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:28 AM
Points: 144, Visits: 423

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 »

Permissions