0

I gave the following permission to user1 so user1 can create tables on database1:

GRANT CREATE TABLE TO user1 WITH GRANT OPTION

I verified that user1 can create table.

Then I ran the following command to revoke the above permission:

REVOKE CREATE TABLE FROM user1 CASCADE

But user can still login to database1 and create table. Question: Why user1 can still create a table on the same database1. How can we revoke the CREATE TABLE from user1?

Remark: As stated here REVOKE would have failed if we had not used the CASCADE. But I'm using CASCADE in the above sql

2
  • 3
    What other roles and permissions does user1 have?
    – J.D.
    Sep 13, 2022 at 21:52
  • @nam it turns out "GRANT ALTER TO [MyDbRole]" not only allows altering all tables inside this database1, but also grants alter on database itself. So role can alter the database, and also create tables even if you revoke the "CREATE TABLE" permission from it. Now I am figuring how you can remove the "ALTER" on database-level while still be able to alter all tables inside this database, which you stated is the requirement, in your other question Sep 15, 2022 at 15:29

1 Answer 1

1

Sometime user have permission from other database roles or server role. In this case you shoud deny instead of Revoke.

Also if the user is db_owner or sysadmin then you can not revoke or deny any permission from that user.

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.