Search code examples
sql-servert-sqlstored-procedurespermissionsuser-defined-types

Can't use User-Defined Table Type as a parameter in procedure


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;

Solution

  • 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;