Allow user to grant select on views?
-
Hello all,
I have a db user with restricted privileges (mostly insert/update/select and create view on database level, as well as alter on schema level) that is used for several (SSIS) data load processes. This user needs to create some views within the dbo schema and grant select permissions on them to other users.
Unfortunately I can't get this user to grant select permissions to the new views, without granting him control privileges on the schema (which is not possible in this case). I tried
grant select to _USER_ with grant option
as well as
grant select on schema::dbo to _USER_ with grant option
but it seems that the "with grant option" is not inherited to securables of a lower level. If this user executes e.g.
grant select on _VIEW_ to _OTHER_USER_
after he created the view _VIEW_ the resulting error is
Msg 15151, Level 16, State 1, Line 1
Cannot find the object '_VIEW_', because it does not exist or you do not have permission.
Is there any way of getting around this without granting schema control permissions to that user?
Thanks!
Rainer
-
Use this script given below to see if the user has grant option.
select b.name,c.name,
user_name(a.grantee_principal_id) as UserName,a.permission_name,
case a.state
when 'W' then 'With Grant Option'
when 'G' then 'Grant'
When 'D' then 'Deny'
Else ''
end as PermissionType
from sys.database_permissions a,sys.all_objects b,sys.schemas c
where b.object_id=a.major_id
and a.grantee_principal_id<>0
and a.grantee_principal_id<>2
and b.schema_id=c.schema_id
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Sugesh, the user has (as expected) no (explicit) permissions on the views; he can "select" data from the view nonetheless due to his database level select privilege.
As a select privilege on database level is sufficient to issue select statements on all views/tables, my (wrong) assumption was that the "with grant option" allows to grant select permissions on single objects. Unfortunately that user can "only" grant select on database level, which works fine, but is not what is needed. Anyway I need to allow that user to grant select on object level - without control privileges.
Thanks,
Rainer
-
Then give select @ db level and control permissions @ object level so that he can grant permission to other users.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
The user creates new views within the dbo schema, so I cannot give him control permissions on object level because these views do not exist yet.
Regards,
Rainer
-
Use a DDL Trigger on the CREATE_VIEW event. Something like:
GRANT
TRIGGER trigDDL_CreateView
ON DATABASE
WITH EXECUTE AS 'dbo'
FOR CREATE_VIEW
AS
BEGIN
-- Determine the Executing User
DECLARE @UserName nvarchar(256);
SET @UserName = EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(256)');
-- If it's our particular user, grant the SELECT
IF @UserName = 'TestUser'
BEGIN
DECLARE @sql nvarchar(2000);
SET @sql = 'GRANT SELECT ON ' +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)') +
' TO TestUser WITH GRANT OPTION';
EXEC(@sql);
END;
END;
K. Brian Kelley
@kbriankelley -
This is a great idea, thanks!
Rainer
-
Heh... I'd like to know why a "user" is allowed to create anything in a database, especially views.
Improperly written views can bog a database so badly that folks can't even login...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
While I don't disagree with you, because I have that t-shirt, too, sometimes we don't get much of a choice.
K. Brian Kelley
@kbriankelley -
Heh... I guess I'll never understand that...
Rainer, what say thee? It's always an interesting topic... why is it that your users are allowed to create views without going through the DBA?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, probably I was not clear about the purpose of this. The db user is used only within some SSIS packages for automated data loading and (unfortunately) creation of several views, so no human user is going to play around with the database
Regards,
Rainer
-
Heh... ok... not sure that's better or worse but at least its not a desparate human
Thanks, Rainer.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply