CODEWITHSUNDEEP
sql-serverdatabaseuser-management
Fractaliste
Fractaliste

Reputation: 5957

How to give to a user access to a view but not to the table the view is using?

I've the following view defined into myCustomDatabase:

CREATE VIEW myCustomDatabase.dbo.myView AS
   SELECT job_id  FROM msdb.dbo.sysjobhistory

myCustomDatabase's owner is sa.

My current user (called currentUser) has only the db_reader role on the myCustomDatabase.

guest is enable on msdb database.

When I execute the view I get the following error:

The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.

I understand that my current user has no role defined on the system database.

Which role/authorization should I give to my current user to allow him to execute the view (which contains only one column of the system's one), but not give to him full access to the table.

To resume the following view should work when called with currentUser:

CREATE VIEW myCustomDatabase.dbo.myView AS
    SELECT job_id  FROM msdb.dbo.sysjobhistory

, but not the following query:

SELECT *  FROM msdb.dbo.sysjobhistory

Edit : viewable tables in MSDB

enter image description here

Edit2 : My SQLServer version is 2008

Upvotes: 0

Views: 944

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46193

You don't need to grant permissions on the tables referenced by the view as long as the ownership chain is unbroken. In the case of dbo-owned objects in different databases, this requires that:

  1. both databases have the DB_CHAINING option turned on (on by default in msdb)
  2. databases have the same owner ('sa' is the default owner of msdb)
  3. the user has a security context in the other database (guest is enabled by default in msdb)

Consequently, the following script should do the job. 

ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;
ALTER AUTHORIZATION ON DATABASE::myCustomDatabase TO sa;

Note that you should enable DB_CHAINING in sa-owned databases only if you trust privileged users with permissions to create dbo-owned objects. This isn't a consideration if only sysadmin role members can create objects anyway. Also, if the old owner is not a sysadmin role member and you need that login to retain dbo permissions, add the old owner as a regular database user and add to the db_owner role.

Upvotes: 2

Related Questions