One of the problems with GUIs that provide a “user friendly” interface to a command-line-oriented system is that if the GUI designer decided not to implement a particular feature then you’re left scratching your head.

I created a user-defined table in SQL Server today, and tried to use it from a stored procedure.  SQL Server didn’t like that.  It said:

The EXECUTE permission was denied on the object 'MyTableType', database 'MyDatabase', schema 'dbo'.

Okay, fine.  I’ve been here before with stored procedures.  So I pull up the properties for the user-defined table and … there’s no checkbox for the Execute permission.  Huh?

Now I know that SQL Server gurus do all their configuration with T-SQL commands.  That’s cool.  Really.  But I’m just a lowly programmer, not a white-coated DBA.  I stay as far away from databases as I can most of the time ’cause whenever I mess with them I end up with bites and scars.  But we don’t have the luxury of hiring a DBA, so I get to consult Google for how to set permissions.

It turns out not to be too difficult.  Just a few minutes’ looking around gave me this command:

grant execute on type::dbo.MyTableType to MyAppName

And now when I look at that object in the GUI, the Execute permission is shown.  Again I say, “Huh?”

Moral: You can’t administer SQL Server with just the GUI.  You’re going to learn some T-SQL whether you want to or not.