What is the permission needed to add permissions?Receiving “The SELECT permission was denied on the...
Using time travel without creating plot holes
Performance and power usage for Raspberry Pi in the Stratosphere
Is it possible to detect 100% of SQLi with a simple regex?
What if I miss a connection and don't have money to book next flight?
Is there any danger of my neighbor having my wife's signature?
Can you say "leftside right"?
Alternate timeline nomenclature
In a post apocalypse world, with no power and few survivors, would Satnav still work?
How long can the stop in a stop-and-go be?
Are all power cords made equal?
Buying a "Used" Router
Renting a 2CV in France
Is the percentage symbol a constant?
How to write Muḥammad ibn Mūsā al-Khwārizmī?
Inversion applicable to three-dimensional lattices only
How do I avoid the "chosen hero" feeling?
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
Is it possible to methodically find the total of ways to read a given phrase making a stack?
Is there a way to pause a running process on Linux systems and resume later?
Why can't I set the 'prototype' of a function created using 'bind'?
Can I use a single resistor for multiple LED with different +ve sources?
Why don't you get burned by the wood benches in a sauna?
How does a mid-19 century Military combat a modernized one?
Why write a book when there's a movie in my head?
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♦ 12 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♦ 12 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♦ 12 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♦ 12 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