Create a Stored Proc for dynamic SQL

Question

Create a Stored Proc for dynamic SQL

caippers

SSC-Addicted

Points: 472
More actions
March 17, 2014 at 3:31 pm

#283487

I'm trying to create a SP on the DataBackup Database. This is my first Stored Procedure I've created and I believe that I have it set up correctly:
use DataBackup
go
Create PROCEDURE dbo.PracOffProvCnt
as
Declare @db nvarchar (max)
Declare @sql nvarchar (max)
select @db = 'BUODS1_' + max(substring(sd.name,8,8)) + '.' +'Mid.Providerpracticeoffice' from sys.databases sd where sd.name like 'BUODS1_%[0-9]'
Set @sql =
'IF Exists (Select providertype from databackup.dbo.PracticeOfficeProviderCnt)
drop TABLE databackup.dbo.PracticeOfficeProviderCnt
create table databackup.dbo.PracticeOfficeProviderCnt(
ProviderType nvarchar(20),
ProviderCount int)
Insert into databackup.dbo.PracticeOfficeProviderCnt (ProviderType,ProviderCount)
Select ''ALT'' as ProviderType, cast(COUNT(B.ProviderID)as INT) as ProviderCount
from ODS1Stage.Show.SOLRProvider B
join ODS1Stage.Mid.ProviderPracticeOffice A on B.ProviderID = A.ProviderID and b.providertypegroup = ''ALT''
join '+@db+' C on C.ProviderID = A.ProviderID
where B.ProviderID not in (select ProviderID from ODS1Stage.Base.ProviderReasonEndDate)
and a.ProviderOfficeRank = 1 and C.ProviderOfficeRank = 1
and (a.ZipCode <> C.ZipCode
or a.State <> C.State
or a.City <> C.City
or a.AddressLine1 <> C.AddressLine1)'
--print @sql
exec sp_executesql @sql
go
execute dbo.PracOffProvCnt
When I try to execute the SP I receive the following error message:
Msg 229, Level 14, State 5, Procedure PracOffProvCnt, Line 1
The EXECUTE permission was denied on the object 'PracOffProvCnt', database 'DataBackup', schema 'dbo'.
Is my SP set up correctly? From what I see it is and I am having a permissions issue on this DB.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

x SSC-Insane Points: 23660 More actions · Answer 1

Nothing jumps out as incorrect, but since I can't recreate your exact scenario, hard to troubleshoot from here.

Anyways, I know I might be typing the obvious but if you can't succeed with the entire job (getting that stored procedure safely saved and executing it), then break the job down into parts, and see whats failing, its either the creating an executable stored procedure, creating one with that name, or creating THAT executable stored procedure with any name, or creating THAT stored procedure with THAT name.

Try not to get stuck with just failing to get the whole stored procedure to work, try to narrow down which part of your task isn't working.

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 2

It is possible to give you permission to create an object without giving you permission to touch the object. The most simplest way this can occur is by making you a member of the db_ddladmin role. This role can create, alter, or drop any user object in the database. However, this role does not give permission to the objects themselves.

Therefore, if this was done, you would get the behavior you're experiencing.

K. Brian Kelley
@kbriankelley

caippers SSC-Addicted Points: 472 More actions · Answer 3

Thanks all. It actually was my permissions on the DB..... Rights granted and SP working!