grant permission

Question

i've got a database-role, -login and user

CREATE LOGIN dummyLogIn WITH PASSWORD = 'dummyPassword', CHECK_POLICY = OFF;
CREATE USER dummyUser FOR LOGIN dummyLogIn;
CREATE ROLE "dummyRole";

with user related to the role:

EXECUTE sp_addrolemember N'dummyRole', N'dummyUser';

everything fine to this point.

now i grant select WITH GRANT OPTION to the group for a specified table:

GRANT SELECT ON "dbo"."mytable" TO dummyRole WITH GRANT OPTION;

still everythings ok. if i lookup preferences of role in ssms, i see "dummyUser" is a member of this role and the role hast SELECT permission on myTable, including the permission to grant select ( both checkboxes are tagged in preferences-window )

if i try to grant select permission to another group, which is definetely existing i get an errormessage ( no 15151 ): search for object "myTable" not possible, because object doesn't exist oder neccessary permissions don't exist. ( errormessage was in german ;) ).

i can easily do a select on the mentioned table - so i assume, the object DOES exist. and i should be able to grant select permission to another group, in my opinion.

@appended:

query which throws the error:

GRANT SELECT ON "dbo"."mytable" TO childDummyRole ( logged in as user dummyUser )

childDummyRole and referring objects have been created in the same way as the above mentioned objects:

CREATE LOGIN childDummyLogIn WITH PASSWORD = 'childDummyPassword', CHECK_POLICY = OFF;
CREATE USER childDummyUser FOR LOGIN childDummyLogIn;
CREATE ROLE "childDummyRole";

with user related to the role:

EXECUTE sp_addrolemember N'childDummyRole', N'childDummyUser';

Answer 1

You are receiving that error message because dummyUser does not have the proper permissions. In order for that account to grant permission to another user you will have to grant the dummyUser security permissions.

You can review the documentation for GRANT here to find out what exact permission is needed.