"with grant option" does not work on database role?
-
Hello all,
I've got a problem with the "with grant option" on a database role. All users who are members of a specific database role should be able to grant select permissions to other users. Unfortunately the "with grant option" does not seem to work with the database role, see script below:
-- create test role and grant privileges
USE [app]
CREATE ROLE [role_test] AUTHORIZATION [dbo]
grant alter to role_test
grant delete to role_test
grant execute to role_test
grant insert to role_test
grant update to role_test
grant select to role_test with grant option
grant select on testtable to role_test with grant option
-- create test user as member of above role.
USE [master]
GO
CREATE LOGIN [testuser] WITH PASSWORD=N'a1B2c3D4', DEFAULT_DATABASE=[app], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [app]
GO
CREATE USER [testuser] FOR LOGIN [testuser]
GO
EXEC sp_droprolemember N'db_owner', N'testuser'
EXEC sp_addrolemember N'role_test', N'testuser'
GO
-- execute the following as user "testuser", assuming that the user "dummy" already exists in the database
grant select on testtable to dummy
--> Cannot find the object 'testtable', because it does not exist or you do not have permission.
-- explicitly grant the select w/grant permission to the user (e.g. w/ dbo user)
grant select on testtable to testuser with grant option
-- try again the following with the testuser
grant select on testtable to dummy
--> works
Even while the role has permission to grant select on the testtable, a user who is member of that role cannot grant select to another user. I scanned BOL but could not find any hint on special permissions required for roles.
Thanks for your support,
Rainer
-
Rainier,
Everything you did was right, members of role_test just need to use the "AS <database_principal>" clause when granting select on testtable.
Try this (assuming testtable is in the dbo schema)
-- execute the following as user "testuser", assuming that the user "dummy" already exists in the database
grant select on dbo.testtable to dummy
as role_test
From BOL:
AS <database_principal>
Specifies a principal from which the principal executing this query derives its right to grant the permission.
The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
-cjz
-
Thanks Carley. Apparantly I was thinking too much in Windows "user groups". Unfortunately this behaviour of SQL Server is not very user friendly because you have to "know" where your permission to do something comes from. In a complex environment with many user groups and several authorization levels this means a major headache
Kind regards,
Rainer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply