I am working on a database in SQL Server and want to create a procedure similar to this:
create procedure db.insert_data
@variable db.user_defined_type readonly
as
begin
-- code
end;
This fails with the following error:
Msg 15151, Level 16, State 1, Procedure insert_data, Line 2
Cannot find the type 'user_defined_type', because it does not exist or you do not have permission.
However all the examples below work, which basically are creating a procedure without a user - defined table as parameter and running it, and using a user - defined table for a variable and inserting / selecting data from it:
create procedure db.insert_data
@variable int
as
begin
-- code
end;
go
exec db.insert_data '1'
declare @variable db.user_defined_type
insert into @variable (col1, col2)
values (1, 2)
select * from @variable
For permissions, I have been added to a certain role - group which has been granted pretty much every action on the databases. Is this a permission issue? Is there a particular permission that I need for this to work? If yes, how can I check which one to request for it?
Adding the whole script with CREATEs below as well for troubleshooting (I create a table which I use the procedure for too):
create table db.test_table(
col1 nvarchar(3),
col2 nvarchar(10),
col3 nvarchar(35),
col4 nvarchar(35),
primary key (col1, col2)
);
GO
create type db.test_type as table(
col1 nvarchar(3),
col2 nvarchar(10),
col3 nvarchar(35),
col4 nvarchar(35),
primary key (col1, col2)
);
GO
create procedure db.test_insert
@test_var db.test_type readonly
as
begin
--code
select null
end;
My permissions were granted through a database role. The role didn't have REFERENCES permission, hence I couldn't use parameters in my procedures.
Admin granted the role REFERENCES permission through the code below and I was able to create the procedure:
USE db_name; GRANT REFERENCES TO db_role;