May 22, 2012 at 8:48 am
To get around Kerbos double hop.. can you set your view's SQL statement to use its own permissions?
View's statement
SELECT j.job_id, j.category_id, c.name AS category_name, 'P01' AS Server_Name
FROM p01.msdb.dbo.sysjobs AS j INNER JOIN
p01.msdb.dbo.syscategories AS c ON j.category_id = c.category_id
UNION
SELECT j.job_id, j.category_id, c.name AS category_name, 'P02' AS Server_Name
FROM p02.msdb.dbo.sysjobs AS j INNER JOIN
p02.msdb.dbo.syscategories AS c ON j.category_id = c.category_id
UNION
SELECT j.job_id, j.category_id, c.name AS category_name, 'P03' AS Server_Name
FROM p03.msdb.dbo.sysjobs AS j INNER JOIN
p03.msdb.dbo.syscategories AS c ON j.category_id = c.category_id
UNION
SELECT j.job_id, j.category_id, c.name AS category_name, 'SQDP01' AS Server_Name
FROM SQDP01.msdb.dbo.sysjobs AS j INNER JOIN
SQDP01.msdb.dbo.syscategories AS c ON j.category_id = c.category_id
May 23, 2012 at 10:45 am
I beleive you can. Pull it up in Enterprise Manager, click properties, permissions, and you can set them.
May 24, 2012 at 9:23 am
You can set permissions on a view. This can be very useful for several reasons:
1) Lets you hide the complexity of joins, lookups, etc. in your code
2) Lets you apply different permissions to the view object than from the original objects (tables).
3) Lets you restrict the information that is returned by the view, by using only the columns you want to show them (maybe hide the salary column from the employees table), or by only giving certain rows that match certain criteria (such as only the rows for certain groupID's).
One main thing to remember is object ownership chaining. As long as the view is owned by the same owner as the underlying tables, then you just have to give the user access to SELECT on the view. If they are different owners, you do have to give the user access to the underlying tables.
Therefore, as long as the View and the Tables are owned by, say, dbo, then you are cool just giving the users SELECT rights on the view.
Hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply