security configuration

Question

Hi All.
I want to give a user ([test user]) permissions to read data only from specific schema (schema: test) in specific DB (db: ods) in sql server.
it's need to be only with windows authentication login and not with sql server authentication.

i created a login (with public role)
created user inside the DB
and mapped the user to the login.
i give the user default schema :test
and grant the user permissions: GRANT SELECT ON SCHEMA :: test TO [test user]

it's not working, the user can see all the schemas inside the db.

then i tried to do the same with sql server authentication.
and now he can see only objects with :test schema.
but when trying to select the view, it's failed because the user don't have permissions to the tables that created the view.

what is the best way to do it?
give a user ([test user]) permissions to read data only from specific schema (schema: test) in specific DB (db: ods) in sql server. only with windows authentication login and not with sql server authentication?

Answer 1

Hi @bababalush ,

Did you grant the permission on database level?

I did a test in my environment. Please follow below steps ;

1.created an user at server level under Security
2.Server Roles=Public (Default)
3.User Mapping--> Checked on my BD and left Database Role Member for my DB=Public (Default)
4.Expanded my DB-->Security--> and i can see newly created user is there.
5.Opened a new session under this DB and executed GRANT SELECT ON SCHEMA :: [test] TO [test user]
6.That is all. It will works as expected. Once I logged in with new user, I was able to see objects only under Schema test.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

The solution is the same in both cases. The difference is that with SQL authentication, you have full control since the user exist only in SQL Server. With an AD user, you need to consider that the user may be member of an AD group that has wider permissions in the database. If this is the case, you should probably review whether you want to have in that way.

One way to investigate this is to run:

EXECUTE AS LOGIN = 'domain\user'
go
SELECT * FROM sys.user_token
go
REVERT

This will list all tokens associated with this user on database level. This includes AD groups the user is a member of, but also database roles the user is member of, directly or indirectly.