March 17, 2009 at 10:46 pm
Hi,
This is my firts post to this forum and hope you can help me with this one.
I am quite new to DBA. I have multiple databases on a SQL2005 server and each database has its own SQL login.
Let say:
I have (Databse Login) DB1 LOGIN1, DB2 LOGIN2, DB3 LOGIN3
My problem is that all databases are visible when LOGIN1 conect to the SQL server in Management Studio, same for LOGIN2 and LOGIN3
Is there a way to hide the databases that are not for a specific user? Hence, DB2 and DB3 will be hidden (or not showing) from LOGIN1
Any help will be appreciated
Thanks
John
March 17, 2009 at 10:50 pm
Go to login properties-->user mapping and select the only database that the login require.When login 1 connects he'll see db1 only.
March 17, 2009 at 11:06 pm
Thanks Reddy
I have checked what you suggested and it is all set that way. But LOGIN1 can still see all other databases.
What I meant is the databases are visible not accessible.
Any thing you think I should check?
Thanks
March 17, 2009 at 11:12 pm
Thats it...
Why you want to hide them?you can make them not accessable...
In 2000,query analyser one can only see the databases they have access and in 2005 Mgmt studio its not possible.
Anyone pls suggest...
March 17, 2009 at 11:13 pm
Thanks again Reddy
March 18, 2009 at 1:03 am
Right in 2005; when someone logs in to SQL Server using SSMS it queries the database catalog table so it can view. For testing I created a new login with only connect permissions ...
Logged in to SSMS ..
1) Checks Version and Sysadmin permissions.
2) Sets timeout settings.
3) selects server properties.
4) selects policy information.
5) select sever role permissions.
6) selects collation name.
7) Checks SQL Agent roles.
8) Gets configuration information
9) Gets list of all databases on server using following statement ...
SELECT dtb.name AS [Database_Name],
'Server[@Name=' + QUOTENAME(CAST(SERVERPROPERTY(N'Servername')AS SYSNAME),'''') + ']' + '/Database[@Name=' + QUOTENAME(dtb.name,'''') + ']' AS [Database_Urn],
CASE WHEN DATABASEPROPERTY(dtb.name,'IsShutDown') IS NULL THEN 0x200
ELSE 0
END |
CASE WHEN 1 = dtb.is_in_standby THEN 0x40
ELSE 0
END |
CASE WHEN 1 = dtb.is_cleanly_shutdown THEN 0x80
ELSE 0
END |
CASE dtb.state WHEN 1 THEN 0x2
WHEN 2 THEN 0x8
WHEN 3 THEN 0x4
WHEN 4 THEN 0x10
WHEN 5 THEN 0x100
WHEN 6 THEN 0x20
ELSE 1
END AS [Database_Status],
dtb.compatibility_level AS [Database_CompatibilityLevel],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM MASTER.sys.databases AS dtb
WHERE (CAST(CASE WHEN dtb.name IN ('master','model','msdb','tempdb') THEN 1 ELSE dtb.is_distributor END AS bit)=0 AND CAST(ISNULL(dtb.source_database_id, 0) AS bit)=0)
ORDER BY [Database_Name] ASC
If you notice there is no check for permissions ...
Edit: Fixing casing in statement.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 3:34 am
You can get all the users you want to hide databases from into a Windows group.
You then grant SQL logon to the group, then issue a 'DENY VIEW ALL DATABASES TO group'. The people in the group will only be able to see master and tempdb, but will be able to connect to the databases they are authorised to use.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 30, 2009 at 1:33 pm
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273830
--------------------------------
Add a server permission VIEW DEFINITION to apply to a database. Maintaining it on its own would not be very fun,
and users would expect automatic update of the state,
as users are added or dropped. This can certainly get really
tricky when databases are restored or brought online. Maybe there could be a background task to handle this.
But even a difficult VIEW DEFINITION per database would be
worthwhile for security-minded DBAs, and would make it possible to extend the philosophy for other objects to extend to databases.
--------------------------------
After removing the grant permission to the “VIEW ANY DATABASE” and granting “VIEW DEFINITION” on the databases the login has access to, they are still not listed in the explore window in SSMS? Is there something else I can add to the Login to display the one or two database the login has access to?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply