sql - REVOKE GRANT OPTION from a user

Question

0.00/5 (No votes)

See more:

Hi.

I'm using sql-server 2005;

there is a DB and a Table in it named myTB,
there are two users named user1 and user2.

user2 can log in and set some permissions, for example he can execute the query bellow:

DENY permission ON myTB TO user1. or GRANT permission ...

Now the problem is that I want to restrict user2 ! I mean that user2 shouldn't have the permission to GRANT or REVOKE or DENY new permissions !

thanks in advance.

Posted 1-Mar-13 0:18am

Mohamad77

Add a Solution

3 solutions

Solution 1

Hi

SQL

GRANT <permission> [ ,...n ] ON 
    [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]
    TO <database_principal> [ ,...n ] 
    [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission> ::=
    ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]

<database_principal> ::= 
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login

Don't Give [WITH GRANT OPTION] then user 2 can't Give rights to user1.

Check the following link

SQL GRANT Command[^]
Giving and removing permissions in SQL Server[^]

Regards,
GVPrabu

Posted 1-Mar-13 0:22am

gvprabu

Updated 1-Mar-13 0:30am

v2

Comments

Mohamad77 2-Mar-13 0:15am

thanks, but the grant permission is previously granted to user2 ! NOW how can I revoke this permission which is previously granted !?

gvprabu 2-Mar-13 0:19am

yes , then u will REVOKE the permission then again give the permission to user 2

Mohamad77 2-Mar-13 0:44am

oh, YES, you're right ;) thanks alot

gvprabu 2-Mar-13 0:45am

:-) :-) :-) welcome...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MalwareTrojan · Accepted Answer · 2013-03-01T19:36:00

Solution 3

SQL

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

Posted 1-Mar-13 19:36pm

MalwareTrojan

Mohamad77 · Accepted Answer · 2013-03-01T18:45:00

Solution 2

thanks. solved:

use myDB revoke grant option for select from user2

Posted 1-Mar-13 18:45pm

Mohamad77