none
Table Value Parameter and security RRS feed

Answers

  • This seems a little bit of a missing case, I will follow up with the product team. Most likely we will add the ADD/DENY/REVOKE permissions to the TYPE class in the future, meanwhile in order to work around it, you can grant schema permissions to the non-admin user where the new TYPE belongs (example below).

    Thanks for your observation and for using SQL Azure.
    Alejandro Hernandez.

    create schema myTypes

    go

    create type myTypes.MyTable as table (col1 int, col2 float)

    go

    create proc dbo.MyProc

          @MyTableParam myTypes.MyTable readonly

    as begin

    select * from @MyTableParam

    end

    go

    declare @myTable myTypes.MyTable

    insert into @myTable values(1,2),(2,3),(3,4)

    go

    create user MyTestUser without login

    go

    GRANT EXECUTE ON Object::dbo.MyProc TO MyTestUser

    GRANT EXECUTE ON Schema::myTypes TO MyTestUser

    execute as user ='MyTestUser'

    go

    declare @myTable myTypes.MyTable

    insert into @myTable values(1,2),(2,3),(3,4)

    exec dbo.MyProc @myTable

    go

    revert

    go

    -- End of demo

    drop user MyTestUser

    drop proc dbo.MyProc

    drop type myTypes.MyTable

    drop schema myTypes

     

    • Proposed as answer by ahs101 Friday, October 30, 2009 7:59 PM
    • Marked as answer by Cloud Azure Friday, October 30, 2009 8:04 PM
    Friday, October 30, 2009 7:59 PM

All replies

  • SQL Azure does support Grant/Revoke/Deny.  We only support it partially. (see http://msdn.microsoft.com/en-us/library/ee336285.aspx).  So you should have the ability to set the permissions you need for TVPs.

    Rick Negrin
    SQL Azure
    Friday, October 30, 2009 5:47 PM
    Moderator
  • I should but SQL Azure does not allow me to do so. I order to use TVP you need to add the necessary permissions.

    Quote from http://technet.microsoft.com/en-us/library/bb510489.aspx

    Security

    Permissions for table-valued parameters follow the object security model for SQL Server, by using the Transact-SQL keywords: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, and REVOKE.

    Friday, October 30, 2009 7:20 PM
  • This seems a little bit of a missing case, I will follow up with the product team. Most likely we will add the ADD/DENY/REVOKE permissions to the TYPE class in the future, meanwhile in order to work around it, you can grant schema permissions to the non-admin user where the new TYPE belongs (example below).

    Thanks for your observation and for using SQL Azure.
    Alejandro Hernandez.

    create schema myTypes

    go

    create type myTypes.MyTable as table (col1 int, col2 float)

    go

    create proc dbo.MyProc

          @MyTableParam myTypes.MyTable readonly

    as begin

    select * from @MyTableParam

    end

    go

    declare @myTable myTypes.MyTable

    insert into @myTable values(1,2),(2,3),(3,4)

    go

    create user MyTestUser without login

    go

    GRANT EXECUTE ON Object::dbo.MyProc TO MyTestUser

    GRANT EXECUTE ON Schema::myTypes TO MyTestUser

    execute as user ='MyTestUser'

    go

    declare @myTable myTypes.MyTable

    insert into @myTable values(1,2),(2,3),(3,4)

    exec dbo.MyProc @myTable

    go

    revert

    go

    -- End of demo

    drop user MyTestUser

    drop proc dbo.MyProc

    drop type myTypes.MyTable

    drop schema myTypes

     

    • Proposed as answer by ahs101 Friday, October 30, 2009 7:59 PM
    • Marked as answer by Cloud Azure Friday, October 30, 2009 8:04 PM
    Friday, October 30, 2009 7:59 PM