Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, April 3, 2015 1:22 PM
Hello,
We have several databases on SQL server 2012 . We would like to create 1 new user that can see database 'X' but can not see the rest of the databases. User should have only db_datareader permissions .
Ps. We cannot change current owner of database X.
Any idea ? Thanks.
Kr,
Marek
Friday, April 3, 2015 3:27 PM ✅Answered
We have several databases on SQL server 2012 . We would like to create 1 new user that can see database 'X' but can not see the rest of the databases.
Not possible. (Don't feel bad, this is a very valid request, and I don't understand why Microsoft don't act on it.) Here is a Connect item you can vote on:
https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database
You can do REVOKE VIEW ANY DATABASE for public or DENY this permission for this user, but the user will have to know the name, and type "USE mydb" in a query window.
But even if you do DENY VIEW ANY DATABASE, the user can still find out the name of all databases with help of the db_name() function. From what I gather, this will be addressed in the next version of SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, April 3, 2015 1:30 PM
you can give that user db_reader permissions on that database. if the user does not permission to access othe database, even though see them he cannot access.
There is a way to not let the user see the database in ssms object explorer but it applies to all databases the user does not own. since the database is not owned by the user, even though the user has reader permisssions , he cannot see it in the object explorer..
if you want that, try Deny view any database to <<user>>
Hope it Helps!!
Friday, April 3, 2015 1:57 PM
And this is exactly problem that I want to solve.
When I have db reader access only to one db and apply following command :
Deny view any database to <<user>>
I cannot see nothing in ssms object explorer.
The question is how to change it to see only one db with db reader permission to this db in ssms ( without db owner ). I'm sure that its possible.
Kr,
Marek
Friday, April 3, 2015 2:02 PM
And this is exactly problem that I want to solve.
When I have db reader access only to one db and apply following command :
Deny view any database to <<user>>
I cannot see nothing in ssms object explorer.
The question is how to change it to see only one db with db reader permission to this db in ssms ( without db owner ). I'm sure that its possible.
Kr,
Marek
i do not think it is possible, but wouold be curous to know if it possible... when i looked for it, when i wanted, i did not find any way to accomplish..would be nice to know, if possible
Hope it Helps!!
Friday, April 3, 2015 3:02 PM
USE [master]
GO
CREATE LOGIN [readonlyuser] WITH PASSWORD=N'test', DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Test]
GO
CREATE USER [readonlyuser] FOR LOGIN [readonlyuser]
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser]
GO
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Friday, April 3, 2015 3:06 PM
USE [master]
GO
CREATE LOGIN [readonlyuser] WITH PASSWORD=N'test', DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Test]
GO
CREATE USER [readonlyuser] FOR LOGIN [readonlyuser]
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser]
GOBest Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Helloo Uri - Yes, this script gives the user read access on the database - the question OP was asking and in my understanding is that - he does not want to see any other databases in the object explorer other than the one he has acces(read access) for the user. the user is not db_owner.
Hope it Helps!!
Saturday, April 4, 2015 6:23 AM
Ok, if so, see the below,however, this works perfectly if the user is the owner of the database.
• Create a new SQL login "login1"
• Create a user named “login1” in master database
• Grant CREATE DATABASE to login1
• While impersonating login1, create a database called “dbteste”
• Revoke CREATE DATABASE permission from login1
• Revoke VIEW ANY DATABASE permission from PUBLIC
• Register this server as login1
• From the “login1” session, expand database tree. Now, you should see
master, tempdb, dbteste
• Grant VIEW ANY DATABASE to PUBLIC
• From the “login1” session, you should see all the databases
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, April 7, 2015 8:55 PM
Actually contained databases would behave that very way: contained users only see the database itself.
But I would think twice or better three times if I really want to enable them. Make sure you know the implications for administrators.
Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com