July 10, 2020 at 6:46 pm
Hi All,
I have one task to create a login, and user need to view only One Database, there are 20 other databases but requirement is for the user to view only one database and only 5 tables from 1000 tables.
I tried to create a login and give Server Role Public and Database role db_datareader but user can see all databases, not accessible but still see the other database. and also see all of the tables from the database, which i gave him access.
can anyone please guide me?
Appreciate for any input.
Thanks!
July 10, 2020 at 7:16 pm
Offhand, I am not sure why they can see all of the databases if they only have permissions on the one. A quick check online, it sounds like this isn't easily possible.
Which basically shows you how to hide all databases for a specific user, but the user needs to be the database owner in order to see it. Same thing from this link:
https://scatteredcode.net/sql-server-hide-database-list-from-a-particular-user/
As for seeing all tables, you gave them db_datareader role. that lets them read from all tables and views on the database. You will need to either give them explicit permissions on the tables they need access to, or (the preferred way) create a new role and give that role permissions to those 5 tables and put the user in that role.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 14, 2020 at 2:49 pm
Create the login.. make it public. Don´t give any access there.
Go to the Database> Security ( add permisisons there under the account ) > select the account >
add permissions under OWNED SCHEMAS and be more granular under SECURABLES.
Test the account.
Hope this helps.
July 14, 2020 at 5:24 pm
jcarranza's solution will also work; my preference is still to create a role and use that role to assign permissions. What happens to that user/login when they quit and a replacement gets hired in? Or a second person needs their permissions so they can be a backup or secondary person to do that job?
Assigning roles is, in my opinion, a lot simpler. Especially when you have multiple tables to work with for the permissions.
For example, if your QA team needs access to tables A, B, C and D and your QA team is 1 person right now. Giving that person access to tables A, B, C, and D is easy. Then they get a second person on the team, not a big deal adding them again. Now 10 years later, the team has 50 people in it and they now need access to table E, F and G. That is now 50 users you need to add permissions for 3 tables. That will be a huge pain in the GUI, and a lot of scripting to do in TSQL.
Now, lets say you used a role called QA. That role has access to tables A, B, C and D and initially has 1 user in it. Jump forward 10 years and you have 50 people in that role and need to add tables E, F and G? That's easy - add those 3 tables to the role and the users will have access.
Now to make management of this even easier - Create a QA security group in AD and grant that access to the database as a login and user on the database and put it in the QA role. AD group gets the new users and no changes need to be done on the SQL side! Your source of truth for users is AD. User changes departments from QA to IT, they lose access to those tables as soon as the AD admin makes the change.
Also, future DBA's (and future you) will appreciate that you had the foresight to use roles rather than individual permissions. Imagine someone asks you to "replicate" permissions from user A to user B. That is trivial with role-based permissions, but a pain in the butt with object-based permissions.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 14, 2020 at 7:08 pm
One other point about AD groups...you can treat an AD security group as a 'role' in SQL Server instead of creating a role in SQL Server and adding the security group to that role. This does not work well if you have separate roles in SQL Server and need to add those separate roles across users - but generally a security group should define the role a user has in the organization which will dictate what level of access and permissions they have in the database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2020 at 9:13 pm
Hi All,
I tried to create a login and give Server Role Public and Database role db_datareader but user can see all databases, not accessible but still see the other database.
And the reason they can view any database is because by default, public has view any database permission. You'd have to remove that permission from public or deny that permission for that login.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply