SQL Server : View permission to table

Question

Is it possible to grant a user access to a view but restrict the user from accessing the table the view selects from?

CREATE TABLE [dbo].[tUsers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](50) NULL,
    [Password] [nvarchar](50) NULL,
    [TenantID] int
) ON [PRIMARY]

CREATE VIEW [scmTenant1].[vUsers]
AS
SELECT     ID, Username, Password
FROM         dbo.tUsers
WHERE        TenantID = 1

The SQL Server user account (usrTenant1) has access to schema scmTenant1 but does not have access to the dbo schema. I log into SQL Server Management Studio as usrTenant1 and try executing this query:

SELECT * FROM scmTenant1.vUsers

Gives me error:

The SELECT permission was denied on the object 'tUsers', database 'Sandbox', schema 'dbo'.

If I grant this account access to the dbo schema the query executes fine.

I think what I would really like to grant the view permission to the table. But my question is can you grant a user access to a view and restrict that user from accessing the underlying table the view selects from?

podiluska podiluska · Accepted Answer · 2012-10-23T14:38:49

If you make the owner of the view dbo rather than scmTenant - or create an intermediate view that does the same - then you can grant permission to the view without granting permissions to the underlying table.

ie

CREATE VIEW dbo.vUsers_T1
AS 
SELECT     ID, Username, Password 
FROM         dbo.tUsers 
WHERE        TenantID = 1

CREATE VIEW [scmTenant1].[vUsers] as
SELECT * FROM dbo.vUsers_T1

Alternately, you could create a function that returns the ID of the current tenant and create a view that refers to that in dbo.

CREATE VIEW dbo.vUsers
AS 
SELECT     ID, Username, Password 
FROM         dbo.tUsers 
WHERE        TenantID = dbo.GetCurrentTenant()

SQL Server : View permission to table

1 Answers