What is the permission needed to add permissions?Receiving “The SELECT permission was denied on the...
Neglect higher order derivatives in expression
Running away from a conflict
Can I benefit from a feat effect whilst polymorphed?
How can I keep my gold safe from other PCs?
Is the percentage symbol a constant?
How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?
How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?
Is it possible to detect 100% of SQLi with a simple regex?
Third wheel character
Quick Way to Extrude Line to Make Ledges?
How does a mid-19 century Military combat a modernized one?
Is there a way to pause a running process on Linux systems and resume later?
Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?
What is wrong with my use of "find -print0"?
Why can't I set the 'prototype' of a function created using 'bind'?
Disk space full during insert, what happens?
Does Plato's "Ring of Gyges" have a corrupting influence on its wearer?
How do I avoid the "chosen hero" feeling?
Taking an academic pseudonym?
Tikz: Perpendicular FROM a line
How to write Muḥammad ibn Mūsā al-Khwārizmī?
Including proofs of known theorems in master's thesis
Can you prevent a man in the middle from reading the message?
If we can’t finish all tasks, does this mean we are doing Scrum wrong?
What is the permission needed to add permissions?
Receiving “The SELECT permission was denied on the object” even though it's been grantedGrant permissions to run an SQL server jobMaintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?Permissions Needed To Attach AdventureWorks Databasedb_owner unable to drop database - Error 615, SQL ServerExecute permission denied on object sp_start_jobProcedure permission issues with a SQL state queryDeny dbo schema permissionSQL Server: Creating a user database with permissionsHow to setup vault and Postgres in Google cloud to have the correct permissions?
I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.
So my problem is two-fold - I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.
Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.
BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.
sql-server permissions
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.
So my problem is two-fold - I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.
Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.
BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.
sql-server permissions
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
As for the title Grant object permissions The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
– Reaces
Jan 13 '15 at 17:19
add a comment |
I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.
So my problem is two-fold - I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.
Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.
BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.
sql-server permissions
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.
So my problem is two-fold - I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.
Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.
BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.
sql-server permissions
sql-server permissions
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
edited Jan 13 '15 at 17:11
Chris B. Behrens
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
asked Jan 13 '15 at 16:50
Chris B. BehrensChris B. Behrens
1064
1064
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
As for the title Grant object permissions The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
– Reaces
Jan 13 '15 at 17:19
add a comment |
1
As for the title Grant object permissions The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
– Reaces
Jan 13 '15 at 17:19
1
1
As for the title Grant object permissions The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
– Reaces
Jan 13 '15 at 17:19
As for the title Grant object permissions The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
– Reaces
Jan 13 '15 at 17:19
add a comment |
1 Answer
1
active
oldest
votes
To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.
Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
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%2f89186%2fwhat-is-the-permission-needed-to-add-permissions%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
To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.
Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
add a comment |
To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.
Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
add a comment |
To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.
Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.
Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
answered Jan 13 '15 at 21:04
mrdennymrdenny
26k23574
26k23574
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
add a comment |
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
I've added that role to that login - when I try to execute the simple statement "USE {DATABASENAME}", I get the message "The server principal "{USERNAME}" is not able to access the database "{DATABASENAME}" under the current security context."
– Chris B. Behrens
Jan 14 '15 at 18:43
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
OK, I looked up securityadmin in BOL and you still have to be a user within the database to grant rights within the database. So if the user which you are using isn't inside the database, then you'll need to be sysadmin in order to change stuff within the database.
– mrdenny
Jan 20 '15 at 8:28
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%2f89186%2fwhat-is-the-permission-needed-to-add-permissions%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
1
As for the title Grant object permissions The grantor (or the principal specified with the AS option) must have either the permission itself with GRANT OPTION, or a higher permission that implies the permission being granted.
– Reaces
Jan 13 '15 at 17:19