I accidentally removed SELECT permission from my user on SQL Server
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?
sql-server permissions
edited 16 mins ago
Paul White♦
50.9k14278448
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
add a comment |
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?
sql-server permissions
edited 16 mins ago
Paul White♦
50.9k14278448
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
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
add a comment |
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?
sql-server permissions
edited 16 mins ago
Paul White♦
50.9k14278448
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
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?
sql-server permissions
sql-server permissions
edited 16 mins ago
Paul White♦
50.9k14278448
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
edited 16 mins ago
Paul White♦
50.9k14278448
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
edited 16 mins ago
Paul White♦
50.9k14278448
edited 16 mins ago
Paul White♦
50.9k14278448
edited 16 mins ago
Paul White♦
50.9k14278448
50.9k14278448
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
asked Sep 21 '16 at 7:28
iminikiiminiki
1042
1042
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
add a comment |
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
2
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
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
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f150243%2fi-accidentally-removed-select-permission-from-my-user-on-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
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
add a comment |
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.
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
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
add a comment |
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.
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
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.
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
answered Sep 21 '16 at 8:17
Andriy MAndriy M
16k63372
16k63372
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
add a comment |
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
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
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
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
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f150243%2fi-accidentally-removed-select-permission-from-my-user-on-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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