After creating roles and granting privileges to them, I want to grant the privileges of a specified role to a user with grant option so that the user can grant privileges to others, how to do with it?

EXEC sp_addrolemember 'db_owner', 'sabertazimi'

Doing like this can't make 'sabertazimi' to grant his privileges to others.

| improve this question | | | | |

For the specific code you used in the question you cannot use WITH GRANT OPTION which is normally used to grant privilege so the the user can grant same to other users.

For details and example see this: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql


Adding members to flexible database roles requires one ofthe following:

  • Membership in the db_securityadmin or db_owner fixed database role.

  • Membership in the role that owns the role.

  • ALTER ANY ROLE permission or ALTER permission on the role.

    Adding members to fixed database roles requires membership in the db_owner fixed database role.

For privileges using GRANT statement see these links:

| improve this answer | | | | |

As we know:

The GRANT ... WITH GRANT OPTION specifies that the security principal receiving the permission is given the ability to grant the specified permission to other security accounts.

When the principal that receives the permission is a role or a Windows group, the AS clause must be used when the object permission needs to be further granted to users who are not members of the group or role. Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role must use the AS clause to explicitly invoke the role or group membership when granting the permission.

The following example shows how the WITH GRANT OPTION is used when granting to a role or Windows group.

-- Execute the following as a database owner  
EXEC sp_addrolemember TesterRole, User1;  
-- Execute the following as User1  
-- The following fails because User1 does not have the permission as the User1  
-- The following succeeds because User1 invokes the TesterRole membership  
GRANT EXECUTE ON TestMe TO User2 AS TesterRole;  

The full document can be read at this link: GRANT (Transact-SQL)

Hope this help.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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