Granting Permission on Sql server View

Question

I'm trying to grant select permission to a user on a View that select from multiple databases, any suggestion please?? as i don't want to grant access to this user on all database objects  used by this view?

Answer 1

In this case I would use stored procedure with Execute As. Example:

CREATE PROCEDURE MyProcedure
WITH EXECUTE AS 'domain\username'
AS
SELECT * from db1.dbo.mytable;
GO

Answer 2

Assuming all objects are owned by the same user, you could use cross-database chaining.  Turn on DB_CHAINING on the databases used by the view and add the user to the other databases (without object permissions).  Note that only SELECT permissions on the view are needed.  Be aware that for dbo-owned objects, the database owners must be the same in order to maintain an unbroken ownership chain. 

Enable DB_CHAINING only if you fully trust users who have object creation permissions, such as in an environment where only sysadmin role members create objects.

If you want to use a stored procedure like irusul suggested, consider using a certificate instead of EXECUTE AS.  See Erland's thorough article on the subject: http://www.sommarskog.se/grantperm.html.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

btw Dan just missed the l...

http://www.sommarskog.se/grantperm.html

---

Regards,
Mark Broadbent.

Contact me through (twitter|blog)

---

Please click "Propose As Answer" if a post solves your problem
or "Vote As Helpful" if a post has been useful to you

Answer 4

btw Dan just missed the l...

http://www.sommarskog.se/grantperm.html

Thanks for the catch.  I corrected the link.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Thank you Dan,

I turned  on DB_CHAINING on the databases used by the view using the following T-SQL command  and I assigned the Public role to the user .

EXEC SP_DBOPTION "dbname","DB CHAINING", "true"

Microsoft does not recommend to turn on this option, could you please clarify this point? is it harmful ?

Thank you

Answer 6

Database chaining can open for security problems, yes, but it does not have to.

I have a discussion on it here:
http://www.sommarskog.se/grantperm.html#dbchainging

In my opinion Microsoft are sieving mosquitos and swallowing camels in this area. They are very cautious about DB chaining, but they do not use the same language about the Trustworthy option - which potentially is far more dangerous.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Microsoft does not recommend to turn on this option, could you please clarify this point? is it harmful ?

Microsoft cautions that chaining should not be turned on indiscriminately and is why the feature is disabled by default.  This helps avoid security issues in an environment where non-DBA users can create objects, especially dbo-owned ones.  A DBA needs to be mindful that such privileged users could gain access to data in other databases owned by the same login (sic) without direct object permissions when DB_CHAINING is enabled.  But in most production environments, only DBAs have permissions to create objects and so cross-database chaining can be leveraged securely.  The best practice is simply to enable DB_CHAINING only when needed and be aware of the security implications.

I agree with Erland that TRUSTWORTHY is a bigger concern and is why it is also off by default.  As a side note, I have a third-party application that requires a TRUSTWORTHY database and chose to install in on an isolated instance due to security implications even though the vendor was perfectly fine with installing the database on a shared instance.  Security is an important aspect of a DBA’s job.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/