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