SQL Server, Stored Proc to another db / security issues

[guest2013-1]

I have a stored proc on one database that needs to inner join it's one table to another database on the same machine's table.

I'm getting a security context error warning me that the user I'm using to fire this stored procedure doesn't have permission to access the other database (using DBNAME.dbo.TABLE to link to the other database)

Now obviously I can just give that user permissions for that database, but I'm not convinced it's the right way to approach this issue.

Are there any solutions you guys can think of (other than replicating the table) that I could try? It's only the SP I need to have access to the other database/table.

 

[Nerfherder]

I would just give it limited access to that table, just read access...

but replication is an even better idea, not sure why its an issue ?

I'll tell you why its not an issue for me, our DB is on a domain so we give access at an address level. So if its on the same machine then it wont be an issue.

 

[guest2013-1]

Ok I'll play around and see if I can just give read only for that specific table. Replication is a bitch ATM and these are our production financial systems. Since, if this machine goes down, everything goes down, I'm not too concerned about uptime. Actually didn't think of read-only access tbh

 

[MartyMarts]

Linked Server with read-only access for that user at table level sounds like your only option if you can't go the replication route.

 

[Skandalis]

Why replication & linked servers? The databases are on the same machine...

Have a look at cross db ownership chaining, otherwise just grant select on the table on the "other" database

 

[MartyMarts]

Why replication & linked servers? The databases are on the same machine...

Have a look at cross db ownership chaining, otherwise just grant select on the table on the "other" database

If it's the same SQL instance / SQL Server then yes, you're right. I'm guessing I assumed different instances.