Permission on SP
- I need to create a SP in a Database and to make with that it makes a Select in another Database, but I would like that the permission was only for the execution of the SP and in my tests the SP complains of authorization not to make select.
-
I believe the owner needs to be the same. Is the owner of the SP the same as the owner of the table(s) in the other database?
Also, if you are using dynamic SQL within the SP that will force security to be checked and cause your problem.
-
Yes, is the same owner.
Dynamic SQL? What is this?
-
If you are using EXEC( 'put some SQL here' ) or exec sp_executeSQL N'put some SQL here' in order to perform SQL it will run "dynamically". This allows you to build SQL within your SQL and then process it.
-
Does the owner of the stored procedure have the necessary rights to the table you are attempting to access in the other database? If not, you will need to grant those rights. If the person executing the stored procedure is the same as the owner of the stored procedure then you will need to change the owner of the stored procedure and tables to someone else (a private user normally "dbo") and then grant execute permission of the stored procedure to the user. I'm sure there are other ways of getting around that problem, but that is the most obvious way to me.
-
For the following procedure:
use Northwind
Create Proc checkCrossCall
as
Declare @sql as nvarchar(4000)
Set @sql=N'use pubs select * from publishers'
exec sp_executeSQL @sql
This is the easiest solution:
You must grant the user execute permission in Northwind to the proc CheckCrossCall
You Must grant the User Select Permission to the publishers table in pubs
The harder solution is to make an App role but it has been quite some time since i have done that so i will have to think about it and then repost
HTH
Tal McMahon
-
Good call Tal. I've actually done that a few times myself. For some reason I just didn't think about it.
That doesn't work as well for cross database joins, but it sounds like he isn't doing that.
-
All object in the database have "dbo" as owner, and I´m not using a Dynamic SQL, I´m using a SQL Like this:
select * from database2..table1
where id = @id
-
Try changing it to:
select * from database2.dbo.table1
where id = @id
I think SQL Server may consider .. to be an owner change and force a security check.
-
I Try:
select * from database2.dbo.table1
where id = @id
and don´t work
-
If you have the option of creating a stored procedure inside database2 to perform the select then I guess that is the best option I can think of at this point. grant execute permission to that stored procedure as well. Then execute the remote stored procedure instead of performing the remote select. I'm not familiar enough with creating custom roles to address that option as Tal mentioned.
-
OK,
there is something your not saying in all of this or it would work.
this works if your login has permission to use both northwind AND Pubs
use northwind
select * from pubs.dbo.authors
this stored procedure works:
use northwind
Create CrossCall
as
select * from pubs.dbo.authors
Again your login must have permission in BOTH databases.
Embedding this query into a stored procedure does not get away from the fact that the User calling the procedure has to have permission to run the call in pubs.
HTH
Tal McMahon
-
Actually, he doesn't want to grant table access to the user. He only wants to grant execute authority to the stored procedures (which is best). The example you gave simply exports the sql to another context and then runs it using dynamic SQL which must always have explicite authority to the tables being accessed. What I mentioned was to create a new SP in the remote database that runs the specific SQL currently running in the existing SP. This solution should only require only execute permission on the stored procedure.
From what I understand it sounds to me like he should be able to run the SQL as he has it written without any problems, but either I don't understand the security structure well enough (which is entirely possible) or else there is a security setting he has applied that is interfering with the normal process. Whatever the issue, I'm hoping that keeping the SQL local to the remote database within a new stored procedure and then granting execute permissions on that remote stored procedure will suffice.
I did a little more research after writing the above. It is my guess cross database ownership chaining is turned off. It is apparently turned off by default and should not be turned on unless you need to do so (according to Microsoft). If that is the source of your problem then I believe the stored procedure approach I gave you will be your best bet. Following is a link to what reminded me about the cross database ownership chaining setting. The part in question is all the way to the bottom.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx
-
Ricardo,
Check cross database ownership chaining as Aaron suggests and also make sure that dbo is mapped to the same login in both databases. I make a point of making SA the dbo in all databases.
Greg
Greg
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply