How to allow a user to select data through a view but not restrict access to underlying table??

Question

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

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

rockstar283 SSCrazy Points: 2704 More actions · Answer 1

I created a login 'test_login'

I created a user 'test_user' for the login 'test_login'

I created a role named 'test' and added 'test_user' to that role.

I granted select and execute to the role 'test' on the view.

BUT still the following is not working

SELECT * FROM DB2.dbo.Test_view --Not working --This should work

SELECT * FROM DB1.dbo.Source --Should Not work --This is fine

Any help guys??

rockstar283 SSCrazy Points: 2704 More actions · Answer 2

We ended up as below:

USE db1

GO

-- Step 3

CREATE VIEW VW1

AS

SELECT *

FROM TBL1

-- Step 4

GRANT SELECT ON VW1 TO test_user

USE db2

GO

--Step 6

CREATE VIEW v2

AS

SELECT * FROM VW1