Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aaarrrgggghhh
Starting Member
6 Posts |
 Posted - 2004-10-27 : 11:24:29
|
| Hi.I've got a bit of a problem using federated databases...I have 2 databases on 2 different servers. In Database1, there is a table called Stuff1. In Database2 there is a table called Stuff2. Also on Database1 is a view called Stuff which is the union of the 2 tables. There is a stored procedure in Database1 which does a select from the view Stuff.I have a SQL User for each database. The 2 servers are linked and the 2 users are mapped to each other using sp_addlinkedserver and sp_addlinkedsrvlogin.User1 has execute permission on the stored procedure, but when I log in as User1 and try and execute the stored procedure I get the following error:"Server: Msg 229, Level 14, State 71, Procedure StoredProc_sp, Line 2SCHEMA LOCK permission denied on object 'Blah2', database 'Database1', owner 'dbo'."I can solve this by giving User2 select permission for Stuff2 in Database2, however I don't want to do this for security reasons.Any ideas as to what is going on? It all seems odd to me as you should be able to deny permissions on tables but grant them on stored procedures however this does not seem to work across my 2 linked servers for some reason. Help please? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
aaarrrgggghhh
Starting Member
6 Posts |
Posted - 2004-10-28 : 05:45:48
|
| Thanks eyechart. That article was really useful.I've been doing a bit more reading and think I might have narrowed down my problem. Basically the owner of all objects on Server1 is sa (on Server1) and the owner of all objects on Sever2 is sa (on Server2), therefore the ownership chain is broken and SQL Server checks the permission on the table Stuff2. So, to resolve this I have mapped to local sa on Server1 to the sa on Server2, thinking this would be the answer to my problem, but it doesn't seem to help.Any further ideas on where I'm going wrong? |
 |
|
|
|
|
|
|
|
SQL Linked Servers permissions problem