locked
How to assign the necessary preveliges to table valued inline function? RRS feed

  • Question

  • Hi,

    I logged in as a sysadmin and created a tabled valued function to get database sizes as under-:
    CREATE FUNCTION [dbo].[get_database_Sizes] ( @Type_Of_Db varchar(10))
    RETURNS TABLE
    AS
    RETURN
    (
      SELECT DB_NAME(database_id) AS DatabaseName,(size*8)/1024 as SizeInMB,getdate() as Date_of_Trn FROM sys.master_files where type_desc = @Type_Of_Db
    )


    I created a login LimitedUser and added the login to the master

    For the user I assigned previliges as follows-:
    use [master]
    GO
    GRANT CONTROL ON [dbo].[get_database_Sizes] TO [UserLimited]
    GO
    use [master]
    GO
    GRANT REFERENCES ON [dbo].[get_database_Sizes] TO [UserLimited]
    GO
    use [master]
    GO
    GRANT SELECT ON [dbo].[get_database_Sizes] TO [UserLimited]
    GO
    use [master]
    GO
    GRANT SELECT ON [sys].[master_files] TO [UserLimited]
    GO

    Then I logged in as LimitedUser and issued the command as follows-:
    select * from dbo.get_database_Sizes('Rows')
    No Rows were returned.

    However when I assign the db_owner role to the user rows are returned when I issue the select * from dbo.get_database_Sizes('Rows') command.

    My intention is just to assign the select prevelige to the user and retreive the rows from the tabled valued function.
    How can I acheive it?

    Thanking you in anticipation.
    Binny Mathew

    • Moved by Olaf HelperMVP Sunday, April 19, 2015 8:50 AM Moved from "Database Engine" to a more related forum
    Thursday, April 16, 2015 10:44 AM

Answers

  • if you want to do it as function,  you need to give limited user both select permission on the function as well as "View any definition" permission. but granting view definition permission, grants access to other metadata objects as well.

    so, one otherway  is to make a procedure with execute as owner  and grant permissions to execute the procedude to the limiteduser...

    try this..

    Create procedure  [dbo].[usp_get_database_Sizes] ( @Type_Of_Db varchar(10))
    with execute as owner
    as
    begin
    
      SELECT DB_NAME(database_id) AS DatabaseName,(size*8)/1024 as SizeInMB,getdate() as Date_of_Trn
       FROM sys.master_files where type_desc = @Type_Of_Db
    
       end
    
    
    
    grant execute on [dbo].[usp_get_database_Sizes] to limitedUser
    
    --run it as limitedUser
      Execute as login ='LIMITEDUSER'
      select SUSER_SNAME()
      execute [dbo].[usp_get_database_Sizes]  'ROWS'
      Revert


    Hope it Helps!!

    Friday, April 17, 2015 2:41 AM

All replies

  • Hi,

    I logged in as a sysadmin and created a tabled valued function to get database sizes as under-:
    CREATE FUNCTION [dbo].[get_database_Sizes] ( @Type_Of_Db varchar(10))
    RETURNS TABLE
    AS
    RETURN
    (
      SELECT DB_NAME(database_id) AS DatabaseName,(size*8)/1024 as SizeInMB,getdate() as Date_of_Trn FROM sys.master_files where type_desc = @Type_Of_Db
    )


    I created a login LimitedUser and added the login to the master

    For the user I assigned previliges as follows-:
    use [master]
    GO
    GRANT CONTROL ON [dbo].[get_database_Sizes] TO [UserLimited]
    GO
    use [master]
    GO
    GRANT REFERENCES ON [dbo].[get_database_Sizes] TO [UserLimited]
    GO
    use [master]
    GO
    GRANT SELECT ON [dbo].[get_database_Sizes] TO [UserLimited]
    GO
    use [master]
    GO
    GRANT SELECT ON [sys].[master_files] TO [UserLimited]
    GO

    Then I logged in as LimitedUser and issued the command as follows-:
    select * from dbo.get_database_Sizes('Rows')
    No Rows were returned.

    However when I assign the db_owner role to the user rows are returned when I issue the select * from dbo.get_database_Sizes('Rows') command.

    My intention is just to assign the select prevelige to the user and retreive the rows from the tabled valued function.
    How can I acheive it?

    Thanking you in anticipation.
    Binny Mathew

    • Merged by Olaf HelperMVP Sunday, April 19, 2015 8:47 AM Same question from same OP
    Thursday, April 16, 2015 10:32 AM
  • Hi, the minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

    https://technet.microsoft.com/en-us/library/ms186782(v=sql.110).aspx

    hope will help


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Thursday, April 16, 2015 10:40 AM
  • is the same thread

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8e7bc85b-79fc-4885-bb4d-17c5a6bd616e/how-to-assign-the-necessary-preveliges-to-table-valued-inline-function?forum=sqldatabaseengine


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Thursday, April 16, 2015 10:56 AM
  • Hi Pasqual e cegile,

          Thank you for the answer. Would granting the above permission give the user access to all the databases in the instance or as the name implies create databases.?

    Thanking you,

    Binny

    Thursday, April 16, 2015 11:28 AM
  • Hi, Binny

    USE [master] GO CREATE LOGIN [myUser] WITH PASSWORD=N'myUser',

    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO grant view any definition to myUser; go

    This means that the myUser can see all metadata in the instance of SQL Server unless the grantee is denied VIEW DEFINITION or CONTROL permissions at the database scope, schema scope, or for an individual entity such as a table


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Thursday, April 16, 2015 12:25 PM
  • if you want to do it as function,  you need to give limited user both select permission on the function as well as "View any definition" permission. but granting view definition permission, grants access to other metadata objects as well.

    so, one otherway  is to make a procedure with execute as owner  and grant permissions to execute the procedude to the limiteduser...

    try this..

    Create procedure  [dbo].[usp_get_database_Sizes] ( @Type_Of_Db varchar(10))
    with execute as owner
    as
    begin
    
      SELECT DB_NAME(database_id) AS DatabaseName,(size*8)/1024 as SizeInMB,getdate() as Date_of_Trn
       FROM sys.master_files where type_desc = @Type_Of_Db
    
       end
    
    
    
    grant execute on [dbo].[usp_get_database_Sizes] to limitedUser
    
    --run it as limitedUser
      Execute as login ='LIMITEDUSER'
      select SUSER_SNAME()
      execute [dbo].[usp_get_database_Sizes]  'ROWS'
      Revert


    Hope it Helps!!

    Friday, April 17, 2015 2:41 AM
  • Thank you paSquale ceglie.
    Sunday, April 19, 2015 8:05 AM
  • Hi Martin Cairney,

         Thank you for the answer.

         I want the select statement to return a table value so that the application developers can capture it as a table in the Sql Data adaptor. I tried the execute as owner in create function script but gave the following ewrror-:

    Msg 487, Level 16, State 1, Procedure get_database_Sizes_as_owner, Line 13

    An invalid option was specified for the statement "CREATE/ALTER FUNCTION".

    Msg 178, Level 15, State 1, Procedure get_database_Sizes_as_owner, Line 14

    A RETURN statement with a return value cannot be used in this context.

    Is the execute as owner clause be used only in procedure? Is there a way out to use the execute as clause  in a procedure

    and return the value as a table or use the execute as owner clause in a tabled valued function.

    With regards

    Binny

    Sunday, April 19, 2015 8:35 AM