Permissions required for using view that points at different database on same server

Question

Permissions required for using view that points at different database on same server

Steph Locke

SSCrazy

Points: 2963
More actions
July 17, 2012 at 6:40 am

#261220

I've developed a view for use by reporting sys user account. The view is in my Toolbox database and retrieves information from the Helpdesk database. I've assigned SELECT permissions to the view which I thought would be the only requirement but it's saying that the username is not able to access the database Helpdesk. I was under the impression that only SELECT on the view was required.
I can't find any BOL or articles which say differently to the impression I was under, so I was wondering if you had any resources to hand that could provide more information?
EDIT: Using 2008R2, don't want to grant access to source database and can't put views into Helpdesk database as Toolbox functions are required and can't modify the 3rd party db structure at all

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

for a cross database query, the login needs to be either a user in both databases, or in a group that exists in both databases.

so for a specific example, you are saying that a user like 'mydomain\lowell' is a user in both the [ToolBox] database and the [OtherDatabase]?

or is it a SQL user?

did you assign select permissions to the right user/group?

use Toolbox;

GO

CREATE USER [mydomain\lowell] FOR LOGIN [mydomain\lowell]

GRANT SELECT on MyView TO [mydomain\lowell]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steph Locke SSCrazy Points: 2963 More actions · Answer 2

User SSRSReports currently only has access to [Toolbox] and does not have access to [Helpdesk]. I'm not anywhere near a DBA so I granted permissions via the GUI... I granted explicit permission on the view by ticking the GRANT box for the permission SELECT.

I appreciate that I will probably have to get the account granted access to the [Helpdesk] database as SQL Server wouldn't lie to me (often) and the ticket has been duly created, but I'm mainly hoping for some useful resources on views (and probably stored procs) on permission setting in different scenarios.

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Yes, you'll need a user/group in the other database and select permission on any tables that the view references.

You only need select on the view when all tables are in the same database & have the same owner (due to ownership chaining).

Permissions on the underlying tables are not required in this case.

However this doesn't extend across databases, unless you enable cross-database ownership chaining.

I'd consider that a gaping security hole though so I'd leave it well alone. 🙂

There's some more reading here: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

Hope that helps!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

What you are running into is a broken Ownership Chain. By default Ownership Chains cannot continue across database boundaries. Your initial assertion about only granting permissions to the VIEW being sufficient is true in the default configuration of SQL Server when:

1. Cross-database Ownership Chaining is disabled (the default).

2. All tables the view references exist in the same database as the view.

3. All tables the view references are owned by the same database user (usually dbo via the dbo schema).

The most common scenario is when a view is added to the dbo schema and only references tables in the dbo schema. In this scenario your assertion holds up, i.e. you only need to grant SELECT permission to the view to have things work correctly.

For security reasons your users cannot use a view created in Toolbox to select data in Helpdesk.

You have many options to overcome the challenge you are facing, here are some:

------------------------

You mentioned it may not be possible to add a view to Helpdesk but this scenario is aligned with your original assertion so I figured I would add it.

1a. Create user SSRSReports in Helpdesk from same login as SSRSReports user in Toolbox.

1b. Create VIEW in Helpdesk.

1c. Grant SSRSReports in Helpdesk SELECT permissions to the tables the Helpdesk VIEW references.

------------------------

2a. Create user SSRSReports in Helpdesk from same login as SSRSReports user in Toolbox.

2b. Grant SSRSReports in Helpdesk SELECT permissions to the Helpdesk tables the Toolbox VIEW references.

------------------------

Most risky from a security perspective. Consult a DBA before implementing anything, especially this option.

3a. Enable cross-database ownership chaining on Toolbox and Helpdesk databases.

3b. Create user SSRSReports in Helpdesk from same login as SSRSReports user in Toolbox.

3c. Create view in Toolbox that references tables in Helpdesk.

3d. Assumption: view in Toolbox and tables in Helpdesk are all in dbo schema, or in schemas with owners that exist in the both databases.

------------------------

4. Use a certificate linked to a login that has permissions to Helpdesk tables to sign the Toolbox view.

------------------------

5. There are other options...

------------------------

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Steph Locke SSCrazy Points: 2963 More actions · Answer 5

Steph Locke

SSCrazy

Points: 2963

July 18, 2012 at 2:17 am

#1514056

Thanks for the information folks 🙂