revoke statement

[BikeBoy20032004]

I have a db_owner sql account that I want to revoke create permissions from.

I log in as dbo (sysadmin) and execute:


REVOKE
CREATE DEFAULT,
CREATE FUNCTION,
CREATE PROCEDURE,
CREATE RULE,
CREATE TABLE,
CREATE VIEW,
BACKUP DATABASE,
BACKUP LOG

from test_me

But I can still create table, procedures and everything else.

What gives?

Thanx

 

[Corran007]

Check what role they have in the database.

 

[BikeBoy20032004]

no user defined roles, and no specifically set server roles.

 

[JamesLean]

Firstly, REVOKE only removes any explicitly defined GRANTED or DENIED permissions from that user. You need to use DENY if you explicitly want to deny the permissions.

In this case, if you use REVOKE the user will still be able to execute those statements as they are a member of the db_owner role.

Having said that, I know with sysadmin members that it is impossible to deny permissions from them - the sa membership overrides any other permissions checking. I don't know whether the same thing applies at a database level with db_owner members - try it and see.

As a further point, if you don't want the user to be able to execute all those statement why not just remove them from the db_owner role and simply grant them the permissions for the things you DO want them to be able to do? That would make much more logical sense and would make administration much easier.

--James

 

[BikeBoy20032004]

Thanks James, that makes sense.