0

I accidentally removed SELECT and other permissions from my user on SQL Server 2014 via Properties.

I checked all the permissions except the Guest in the Owned Schemas list and also in Membership - including db_denydatareader and db_denydatawriter. That's what made me this mistake. There are some permissions (db_denydatareader and db_denydatawriter) that if you check them, you would remove your own permission without being able to regrant them. I checked all of them.

Cannot alter the role 'sp_denydatareader', because it does not exist or you do not have permission.

Unfortunately I can't use SELECT and other function on my queries or selecting the Properties again in order to uncheck the permissions.

What should I do? I'm not the admin on the box. Is there any way to restore it without asking the sysadmin?

  • 2
    I don't think so. Ask your sysadmin: if your permission is legit, you will have it back – spaghettidba Sep 21 '16 at 7:46
4

Checking all the items in Membership means you are adding yourself to all the roles defined in the database. That includes the sp_denydatareader role, which is probably the main reason you cannot open the Properties box any more.

If you have been given the permission to alter roles previously, it is likely that you have not removed it by your erroneous actions. So, you could just try to remove yourself from the sp_denydatareader role by executing this command in a query window:

ALTER ROLE sp_denydatareader DROP MEMBER your_user_name;

If that succeeds, you can then try opening the Properties box again and undoing your mistakes.

  • Alas... Cannot alter the role 'sp_denydatareader', because it does not exist or you do not have permission. – iminiki Sep 21 '16 at 8:31
  • It doesn't look like you can do without the sysadmin then. Sorry. Or maybe there's another way I'm not aware of. – Andriy M Sep 21 '16 at 8:33

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.