Search code examples
sql-serverfunctionuser-defined-functionssql-grantrole

sql server 2012 - can't grant on select on multi-statement table-valued function

It's my first question here, so I say: hallo!

I have a multi-statement table-valued function (called fn) which (of course) returns a table. I also created a database role (called dbr).

I tried to add permission my dbr can select from my fn.

So I opened the dbr, add my fn to securables, grant on Select and I get an error: Key cannot be null. Parameter name: key (mscorelib)

This is little strange, cause in my fn I have primary key on return-table-variable and also key cannot be null. To be specific key is build with three fields: dateFrom, dateTo, article

Function works perfectly, but I cannot make it available to my dbr members.

Any sugestions?

Solution

  • May be it's a bug of non-patched Management Studio or you click in a wrong place, why don't you use T-SQL instead?

    grant select on dbo.fn to dbr