Grant SELECT to VIEW w/o DB_OWNER privileges?

  • I think I already know the answer to this (i.e., can't be done without re-writing the SPs), but here goes:

    I have a few SPs that will take an archived table and create horizontal partitions that the user (role: Mydb_Admin) can run. One of the SPs deletes a VIEW that combines some of the horizontal partioned tables, then is suppose to give "readers" of the data (role: Mydb_User) the ability to SELECT from the VIEW.

    However, I recently discovered the previous DBA was getting around the permissions problem by just giving the user w/ Mydb_Admin role the db_owner role also!

    Is there a way I can re-create the VIEW w/ horizontal partitioned table(s) and grant select to the "Mydb_User" role without providing "db_owner" permissions?  

    Since the SP deletes and then re-creates the view (PROGRAMMERS!!!! UGH!), I have tried every combination of "GRANT...", but I realize you cannot grant priviledges to yourself, then give them to others!

    Obviously, the easiest process is to re-write the "delete/create view" to an "alter view", but the PTBs (powers-that-be) say "no time, work on other things".

    Since I KNOW someone is going to ask.....

    CREATE PROCEDURE [dbo].[MY_CREATE_VIEW] AS

    DECLARE @SplitTblName char(5)

    DECLARE @CreateViewScript nvarchar(4000)

    DECLARE @GrantPermissionsScript nvarchar(4000)

    DECLARE @Count as Int

    Set @Count=0

     

    DECLARE curTableName CURSOR LOCAL FORWARD_ONLY STATIC    READ_ONLY

      FOR select [Name] from dbo.sysobjects

      where OBJECTPROPERTY(id, N'IsTable') = 1

      And

      OR [Name] like 'Feb%' OR [Name] like 'Mar%'

      OR [Name] like 'Apr%' OR [Name] like 'May%'

      OR [Name] like 'Jun%' OR [Name] like 'Jul%'

      OR [Name] like 'Aug%' OR [Name] like 'Sep%'

      OR [Name] like 'Oct%' OR [Name] like 'Nov%'

      OR [Name] like 'Dec%' )

    SET NOCOUNT ON

    OPEN curTableName

    FETCH NEXT FROM curTableName into @SplitTblName

    set @CreateViewScript = 'CREATE view tbl_View as '

    WHILE @@FETCH_STATUS = 0

      BEGIN

        set @CreateViewScript = @CreateViewScript + ' select * from ' + @SplitTblName + ' union all '

        set @Count=@Count+1

        FETCH NEXT FROM curTableName into @SplitTblName

      End

    set @CreateViewScript = left(@CreateViewScript, len(@CreateViewScript) - len(' union all '))

    Close curTableName

    DEALLOCATE curTableName

    if exists (select * from dbo.sysobjects where id = object_id(N'[tbl_View]') and OBJECTPROPERTY(id, N'IsView') = 1)

      drop view tbl_View -- DROP THE VIEW

    If (@Count>0)

      exec sp_executesql @CreateViewScript

    set @GrantPermissionsScript='GRANT SELECT ON [dbo].[tbl_View] TO [Mydb_User] '

      exec sp_executesql @GrantPermissionsScript -- Cannot grant something you don't have w/o db_ownership?!!

     Thanks!

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • Not sure, but you can give this a try:

    CREATE PROCEDURE [dbo].[MY_CREATE_VIEW] AS

    WITH EXECUTE AS OWNER

    AS BEGIN

        DECLARE @SplitTblName char(5)

        DECLARE @CreateViewScript nvarchar(4000)

        DECLARE @GrantPermissionsScript nvarchar(4000)

        DECLARE @Count as Int

        SET @CreateViewScript = 'CREATE view tbl_View as '

        SELECT @CreateViewScript = @CreateViewScript + ' select * from ' + [Name] + ' union all'    

        FROM

            dbo.sysobjects

        WHERE

            OBJECTPROPERTY(id, N'IsTable') = 1

            AND ([Name] like 'Jan%'

            OR [Name] like 'Feb%' OR [Name] LIKE 'Mar%'

            OR [Name] like 'Apr%' OR [Name] LIKE 'May%'

            OR [Name] like 'Jun%' OR [Name] LIKE 'Jul%'

            OR [Name] like 'Aug%' OR [Name] LIKE 'Sep%'

            OR [Name] like 'Oct%' OR [Name] LIKE 'Nov%'

            OR [Name] like 'Dec%' )

        SET @CreateViewScript = substring(@CreateViewScript, 1, len(@CreateViewScript) - 10)   

        if exists (select * from dbo.sysobjects where id = object_id(N'[tbl_View]') and OBJECTPROPERTY(id, N'IsView') = 1)

          drop view tbl_View -- DROP THE VIEW

        If (@Count>0)

          exec sp_executesql @CreateViewScript

        set @GrantPermissionsScript='GRANT SELECT ON [dbo].[tbl_View] TO [Mydb_User] '

          exec sp_executesql @GrantPermissionsScript -- Cannot grant something you don't have w/o db_ownership?!!

    END

    (It also gets rid of your cursor!)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply