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.

  • 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

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.

  • 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

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.