sql server grant, revoke permission to a user

Question

I wrote a simple c# code that connect to sql-server database and execute a query:

        cmd = new SqlCommand(txtQuery.Text.ToString().Trim(), con);
        cmd.ExecuteNonQuery();

• in my db I have a table named myTB. I have two users too: user1(owner), user2(new user created)

• I logged in (connected to DB) with user2's username and password !

• I can access the tables that created by user1 with the query bellow:

  "select * from user1.myTB"

  (I don't know why I get error with this query:"select * from myTB", forget it now!)

• Now I wanna REVOKE 'select' permission from user2. I mean I don't want user2 to execute the select query on myTB table which is created by user1.

• what should I do is a problem that I'm stuck on it ! I used this query, but nothing changed !

Q1: "Revoke select ON user1.myTB FROM user2"

again user2 can do select * from user1.myTB !!! WHY !?

please help me with this. thanks.

Answer 1

You cannot REVOKE something you did not GRANT. Looks like you want to:

1. investigate and understand why user2 has permission to SELECT
2. possibly DENY permission to SELECT to user2

The permission work like following:

• initialy an user has the poermissions derived from his group mebership (including public roles)
• GRANT explictly grants a privilege
• REVOKE takes back a previously granted priviledge, reverting to the user having the privileges implictily inherited from group(s) memberhip
• DENY denies a privilege

The rules of precedence are that any DENY take precedence over any GRANT or inherited privilege. One can get access through a number of GRANTs but one single DENY will revoke the privilege. You cannot GRANT/REVOKE/DENY permissions to the securable owner (members of db_owner own everything and members of sysadmin own everything on the entire server).

Answer 2

Thanks friends, I've solved it and use DENY instead of REVOKE :

DENY select ON user1.myTB TO user2

Answer 3

user2 is probably getting it's permissions from a role membership.

Run:

use [<YourDatabase>]
GO

exec sp_helpuser

find the user in the first column, and then look at the second column. Is the user a member of db_datareader or db_owner?

If so, you can revoke membership, say for db_datareader, by doing:

exec sp_droprolemember 'db_datareader', 'user2'
GO