I accidentally removed SELECT permission from my user on SQL Server

Question

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?

Answer 1

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.