CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
WhatsThePoint
WhatsThePoint

Reputation: 3635

What permissions to I need to use User Defined Table Types

Background:

I have two accounts for the same database one "master" account and another (sub) account which I use for my website to use to connect to the server so I can manage what the user can and cannot do. A lot of the permissions on this account were already on there before I got access to it so I don't know what exactly all the permissions are, except the ones I have managed.

There is a user defined table type that some one else created, dont ask me why they did,

CREATE TYPE [dbo].[AffectedServiceList] AS TABLE(
    [AffSerName] [nvarchar](200) NULL
)
GO

This type is used in a stored procedure like so

ALTER PROCEDURE [dbo].[Contractsetup]  @OtherParams, --Easier than typing them all
                                       @list        dbo.AFFECTEDSERVICELIST READONLY

The master account can execute and modify this procedure perfectly but the sub account cannot run or modify this stored procedure while the affectedservicelist type is used as it gets the error

Cannot find the type 'AffectedServiceList', because it does not exist or you do not have permission.

I have tried doing this grant execute on type::dbo.AFFECTEDSERVICELIST to subaccount

But it still returns the same error that about the permissions. I have eliminated it to the the permissions as when I try and modify on the master account I just get

Command(s) completed successfully.

Upvotes: 10

Views: 9570

Answers (2)

sepupic
sepupic

Reputation: 8687

To use User Defined Table Type you need EXECUTE or CONTROL permission on it. Your code 

grant execute on type::dbo.AFFECTEDSERVICELIST to subaccount

is correct.

What is missing here is REFERENCES permission:

grant references on type::dbo.AFFECTEDSERVICELIST to subaccount

this is need to be able to reference this type in the code.

To only be able o create a proc using UDT, you only need REFERENCES permission. EXECUTE or CONTROL is needed to be able to execute the code(proc)

Upvotes: 10

WhatsThePoint
WhatsThePoint

Reputation: 3635

After doing a bit more digging the permission I needed to add was not execute it was control.

So instead of doing this;

grant execute on type::dbo.AFFECTEDSERVICELIST to subaccount

I actually needed to do this;

grant control on type::dbo.AFFECTEDSERVICELIST to subaccount

Upvotes: 3

Related Questions