Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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';
share|improve this question

migrated from stackoverflow.com Dec 17 '12 at 16:27

This question came from our site for professional and enthusiast programmers.

1  
You only showed us the SQL commands and setup for the parts that work. Now you need to show us the same things for the user, groups and commands that do not work. –  RBarryYoung Dec 12 '12 at 20:30
    
Are you trying to grant permissions on myTable as user dummyRole? Not enough information in the question as it is now to help you further... –  Josien Dec 12 '12 at 23:45
    
information appended in original posting. i thought, the problem is described in the bold textpart. –  Nico Dec 13 '12 at 7:47
    
Hi Nico, I've merged the identical question you posted here on dba.se with this one migrated in from SO. If you ask any other questions on SO that you decide you want here instead, please flag them on SO for migration rather than cross-posting as that is considerably easier for us to manage! –  Jack Douglas Dec 17 '12 at 18:57

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for your hint. I've overread the fact that you have to use the AS clause. Thought WITH GRANT OPTION would be sufficient. I'll test it after holiday and remember accepting your answer if things are fine! –  Nico Dec 21 '12 at 23:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.