Re: Security on view to other database on same sqlserver

• Previous message: Jake Smythe: "Giving access to manage sql jobs"
• In reply to: Begie: "Security on view to other database on same sqlserver"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Fri, 25 Mar 2005 20:36:14 -0600

> What is the best way to give all users read access to this second database
> so that the view from database 1 to database 2 will work?

One method:

1) grant users (or role) SELECT permissions on the view
2) add users to database 2
3) add role to database 2
4) grant SELECT permissions on the table to the role

If your objects are owned by 'dbo': and you also want to prohibit direct
access to the table, you can alternatively:

1) grant users (or role) SELECT permissions on the view
2) enable guest user in database 2 (sp_adduser 'guest')
3) ensure both databases are owned by the same login
4) If you are running SQL 2000 SP3+, enable cross-database chaining in both
databases. Note that you should do this only if you fully trust users that
have permissions to create dbo-owned objects Also, you should enable
cross-database chaining in an sa-owned database when only sysadmin role
members have permissions to create dbo-owned objects in that database.

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Begie" <Begie@discussions.microsoft.com> wrote in message 
news:2D01DB07-CC20-41C9-83AC-4E5B2AFC54AA@microsoft.com...
>I have a sql server, with serveral databases on.
> On one database a user is in the user list and got access.
> In that database there is a view to a table in a second database.
>
> Now the security question. I want all users to have access to this view. 
> The
> users also use windows authentication.
>
> So far the only thing that works is if i set ourdomain\domain users in the
> security/logins to server rol system administrators, but that is obvious
> something i do not want.
>
> I was thinking about setting the database access for domain users on the
> second database to some kind of role, but this does not work.
>
> What is the best way to give all users read access to this second database
> so that the view from database 1 to database 2 will work?
>
> Thnx for an answer
> 

• Previous message: Jake Smythe: "Giving access to manage sql jobs"
• In reply to: Begie: "Security on view to other database on same sqlserver"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]