July 17, 2007 at 8:02 am
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
July 17, 2007 at 8:04 am
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
July 17, 2007 at 9:16 am
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
July 18, 2007 at 2:19 am
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
July 18, 2007 at 8:49 am
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
July 18, 2007 at 4:04 pm
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
July 19, 2007 at 10:55 am
This is a great idea, thanks!
Rainer
July 19, 2007 at 8:57 pm
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
Change is inevitable... Change for the better is not.
July 20, 2007 at 5:57 am
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
July 20, 2007 at 8:13 am
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
Change is inevitable... Change for the better is not.
July 24, 2007 at 1:38 am
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
July 24, 2007 at 7:48 am
Heh... ok... not sure that's better or worse but at least its not a desparate human Thanks, Rainer.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply