May 18, 2010 at 7:14 am
I am running a 2008 Enterprise edition.
Here is what I want to do. I have 2 databases. The first one is a 3rd party vendor database. the other is a database I created for my users. The users need access to the data in the first database. No problem. Set up a a view, give them read only access.
The problem is, I have to then give the users access to the first database.
If I don't give them access, when the users try to use the view in the second database, they get an error telling them that they don't have access to the first database.
I give them access to the first database, by adding them as a user, and everything is fine. I don't have to give them access to objects in the database, I just have to add them as a user, with public access.
I really don't want to have to add the users logins to the first database, if I don't have to. It could become a pain because I actually want to do this with multiple databases and only allow the users access to a single database, and it could also become an audit issue, as it looks like I am giving users access to a database they shouldn't be in.
Is there any way to avoid doing this, using roles, securables, etc.?
Thanks,
Leonard
May 18, 2010 at 8:40 am
Copy the data between the databases using... replication or triggers or whatever. Then you only have to give your users access to the database they're supposed to have.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2010 at 9:34 am
basically you create a linked server in your sql server for your second database
and after that you could create views to allow users to reach data
and by using this method you don't need to create users at the other database
and you could use sql server security options to control your users
for linked server you may look at the link
http://msdn.microsoft.com/en-us/library/aa213778%28SQL.80%29.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply