Unable to Assign Deny Permissions to Users in Database

Question

Post reply

Unable to Assign Deny Permissions to Users in Database

Ram-294479

SSC Enthusiast

Points: 109
More actions
June 6, 2006 at 10:30 am

#60961

Hey Guys,
Some Help needed. I tried assign deny permission to one of the users in the database, the user is still able to access the specific table that i denied.
The Deny was for select, insert, update.
If anyone of you guyz could throw some lite, would really appreciate it.
Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 1

Can you give some more information about the user & his exesting access to db?

Give the command as well which you are executing to deny access to the user.

------------
Prakash Sawant

http://psawant.blogspot.com

Lowell SSC Guru Points: 323518 More actions · Answer 2

could it be the user was denied access to the table, but has execute permissions on a stored proc that actually does the insert of the data?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ram-294479 SSC Enthusiast Points: 109 More actions · Answer 3

I used the command

DENY SELECT, INSERT, UPDATE ON TABLENAME TO USERNAME

The Deny permission doesnt work at all, i mean the user is able to select the requested data, apart from insert and update. And there are no stored procedures which does the insert too

I tried using the sp_helprotect command to view the privileges where it shows as follows:

Owner Object Grantee Grantor Protecttype Action Column

dbo temptable13 username dbo Deny Select (All+New)

But Still the user is able to access the data from the table.

Mark Yelton SSCommitted Points: 1570 More actions · Answer 4

Is it possible the user is part of the db_datareader group for this database? Or, is in a role that has been granted SELECT permission on the table?

Mark

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Do you also use ROLES? See if the user is a member of a role. For example: John Doe is a user, but is also a member of the HR Group role. Maybe they have permissions via the role.

Do they have DB_owner privileges?

Do you get any error message when you try to DENY them?

-SQLBill

Ram-294479 SSC Enthusiast Points: 109 More actions · Answer 6

Nope there are no roles granting access to the user, infact there s a deny on the user, evenso it still doesnt work. Th user is not in the db_owner privileges.

I dont get any error message. When i use the command sp_protect it shows as the user being denied but when user queries the data he is still able to do it.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

What about server roles? Is the login that the user is mapped to a member of sysadmins?

Greg

Ram-294479 SSC Enthusiast Points: 109 More actions · Answer 8

Nope, the user is not a member of server roles. I made sure of that.

Ram-294479 SSC Enthusiast Points: 109 More actions · Answer 9

By sysadmins did u mean securityadmin which is one of the server roles?

Greg Charles SSC-Forever Points: 45403 More actions · Answer 10

No, I meant the System Administrators server role. My thought was if someone is a member of sysadmin, he/she has permission to do anything in the instance regardless of permissions in a database.

Greg

SQLBill SSC Guru Points: 51440 More actions · Answer 11

Is the person an admin on the local box or domain? If so, they fall under the BUILTIN/Administrators login in SQL Server.

-SQLBill

Ram-294479 SSC Enthusiast Points: 109 More actions · Answer 12

Yeah, the user is not a member of the sysadmin server role.

The person is admin on the local box for his instance of sql server but not on the domain.

SQLBill SSC Guru Points: 51440 More actions · Answer 13

The person is admin on the local box for his instance of sql server [end quote]

If he/she is a local admin on the server that hosts the SQL Server, then they are a sys admin for SQL Server. (by way of BUILTIN/Administrators).

-SQLBill