Permissions problem with stored procedure

[Jerrycurl]

I have a stored procedure that looks something like this:<br><br>CREATE PROCEDURE sp_dosomething @param1 varchar(5) AS<br>&nbsp;&nbsp;if @param1 = &quot;this&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;select * from database1.dbo.table1<br>&nbsp;&nbsp;else if @param2 = &quot;that&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;select * from database2.dbo.table1<br>&nbsp;&nbsp;else<br>&nbsp;&nbsp;&nbsp;&nbsp;select * from database3.dbo.table1<br><br>Each of my users generally has access to only one of the databases (database1, database2, or database3), although some have access to more than one.&nbsp;&nbsp;The problem is that when a user who has access to less than all the databases referenced in the sproc tries to run the sproc (with a parameter of &quot;this&quot, he gets an error about not having permission on database2, even though the line the referenced database2 was not going to be called.&nbsp;&nbsp;It's sort of like the stored procedure is &quot;early binding&quot; all the tables it could <i>possibly</i> use, when what I want it to do is &quot;late bind&quot; only the tables that are <i>actually</i> used.&nbsp;&nbsp;Does all that make any sense?&nbsp;&nbsp;It seems like there must be some way to do this.&nbsp;&nbsp;Anyone?

 

[MalcolmW]

You could have your stored proc call other stored procs, based on the paramter/user.&nbsp;&nbsp;That way would provide a form of &quot;late&quot; binding. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Jerrycurl]

I'd rather not have to do this because I have a number of stored procedures and each one will have about 10 conditional branches leading to 10 different databases.&nbsp;&nbsp;I was hoping there was something like a compiler switch that would do what I want.

 

[MalcolmW]

Have you tried obscuring the table names, by executing a string?<br>CREATE PROCEDURE sp_dosomething @param1 varchar(5) AS<br>DECLARE @database varchar(250)<br>if @param1 = &quot;this&quot;<br>&nbsp;&nbsp;set @database = 'database1'<br>else if @param2 = &quot;that&quot;<br>&nbsp;&nbsp;set @database = 'database2'<br>else<br>&nbsp;&nbsp;set @database = 'database3'<br>EXEC ('select * from ' + @database + '.dbo.table1')<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Jerrycurl]

Yeah, that's what I've been doing so far.&nbsp;&nbsp;It just seems like a hack.&nbsp;&nbsp;I thought this wouldn't be so uncommon a scenario that I'd have to resort to such things.&nbsp;&nbsp;Oh well...