Create user that can only SEE one database, and only select from it?
Solution 1
1) Create the user on the server
2) Add the user to the given database
3) Grant read-only access to the database
USE [master]
CREATE LOGIN [SomeUserName] WITH PASSWORD=N'someStr0ngP@ssword', DEFAULT_DATABASE=[c], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [c]
CREATE USER [SomeUserName] FOR LOGIN [SomeUserName] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datareader', N'SomeUserName'
Solution 2
deny is the default behavior when it comes to permission so if you create a user and add it to the db_datareader role on the necessary db, that's the only permission it will have. It wont be able to access the other databases
EDIT:
use [master]
GO
DENY VIEW ANY DATABASE TO [login]
GO
use [master]
GO
DENY VIEW SERVER STATE TO [login]
GO
that will remove the login's abbility to view the databases. Then go to the one you WANT him to see and make him owner of that DB
Solution 3
Step-1: Create the LogIn
Step-2: Remove all DB view permission from the login
deny view any database to LogInName
Step-3: Give the login authorization of database
alter authorization on database:: DataBaseName to LogInName
Note: No need to specify username or database name with in single quotation marks
Comments
-
Kyle about 4 years
We have several databases on SQL server. We would like to create 1 new user that can see database 'c' but can not see the rest of the databases.
This user should only be able to select from this database, and nothing else.
I have been googleing and searching for a while now, and the closest I found was to deny view any database, and then make them the database owner.But I don't think that will work for limiting them to select, unless is there a way I can deny everything except for select on a database owner?
Thanks in advance for any help!
Edit: SQL Server 2008 R2, by the way.
Edit2: Sorry, I was not clear in my original post. I am looking to make it so when they log in they won't even see the names of other databases, not just that they can't access them.
Thanks.
-
Russell Fox about 12 yearsRemember that in 2008 all users are given the "Public" server-level role which cannot be taken away. If you only want them to ever see one database, and you can't limit their access through stored procedures or other middle tier, you'll have to DENY all other databases to the Public role (which might mess up everyone else).
-
Kyle about 12 yearsI might be misunderstanding, or I may have just not been clear in my original post. I meant the user should not be able to see the other databases as in if they log into management studio, other databases won't even show up. With this, they will still show up, and they just won't be able to enter the databases, correct? Is there anyway to make it so they can't even see the databases?
-
Russell Fox about 12 years"You would need to revoke the permission 'VIEW ANY DATABASE' from the role PUBLIC (SQL SERVER 2005 onwards)". I stole that from another post.... Take a look there for more info.
-
Kyle about 12 yearsSorry, I was not clear in my post. I'm trying to make it so they won't even see the other bases are there, or their names. Not just so they can't access them. Thanks for the help.
-
Andrew Barber almost 10 yearsI'm not sure this answer is even really related to this question. It's wrong, at any rate.
-
Andrew Barber almost 10 yearsHow this is wrong: You say "Revoke", but then your command is "deny". Those are very different things.
deny
will utterly block the user from viewing any database.revoke
would work partially, perhaps... except simply adding the user to the database won't work. They need to be the owner to see it when they don't have theview any
right. -
Moumit almost 10 yearsI do not know why you mark the answer as negative @AndrewBarber.. Any way i am not a sql server geek guy .. but it's work for me... And the answer is focused only on the question that has been asked... for what i googled..
-
Andrew Barber almost 10 yearsAs per your edit: "Remove" isn't a better word at all. It's not a related technical term, and the general meaning is the same as Revoke.
DENY
does not "remove" a permission. It adds a negative permission.REVOKE
removes permissions... includingDENY
permissions. -
userSteve almost 8 years@RussellFox if you revoke all the databases then the user cannot see any. Unless they are made dbo, which is not always desirable. I'm not sure there is a good solution for this problem.