Create a user and restrict access to only one DB Object

Question

Create a user and restrict access to only one DB Object

RCRock

Right there with Babe

Points: 718
More actions
August 19, 2020 at 10:53 am

#3781547

Hi,
I have following requirement.
I want to add one user through Sql Server Logins under Security e.g ASIA-PAC\Testuser and while adding I give default database as EMP.
I have also created one view "VwEMPDATA" under database EMP and the view VwEMPDATA retrieves data from EMP database and another database Sales.
How I can ensure that the new user I created "testUser" should have access to only this view VwEMPDATA and nothing else?
Also while select query against the view VwEMPDATA, it should not throw exception for another database table object Sales.
This user should not be able to access any other view, tables etc.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

The keyword you're looking for is GRANT. Yeah, someone could tell you how to do this in just a couple of seconds but I'll suggest that you read the documentation for GRANT and the privs you can grant with it to learn something new.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RCRock Right there with Babe Points: 718 More actions · Answer 2

Hi Jeff,

I have added the user as described above in earlier post and default database I have given is EMP. The user has just public access.

I added this line to my view

GRANT SELECT ON [dbo].[VwEMPDATA] TO [ASIA-PAC\TestUser]

However as I mentioned, the view has also reference to another database.

I noticed when I login to database using the login ASIA-PAC\TestUser. I can run all the tables under EMP database. This is not what i want. I want that the user ASIA-PAC\TestUser should have access to only new view VwEMPDATA only.

When i use select query against VwEMPDATA i get error

The server principal "ASIA-PAC\TestUser" is not able to access the database "Sales" under the current security context.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

The other key word you need is DENY. In addition to GRANT permissions, you can also DENY permissions.

Sounds like the user might belong to a group that has more access than you've explicitly given. I'd check the groups both at the server and database level to be sure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning