Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've created a stored procedure that creates a database, creates a set of tables, copies all of the data from a linked access database into those tables then creates a user and assigns that user to the db_reader, and db_writer roles. When I run this as sysadmin everything works correctly. When I try to run it as a sql server user I have setup it returns an error saying that user already exists. A look at secuirty on that database does not show the user. When I check the sid for the server_principles the sid for the user I'm trying set is the same sid I find used for dbo on the database_principles table.

I'm at a loss for why it would set up the user differently depending on the user that is running the stored procedure. Is there anyway around this? I've attemtped to run the sp_changedatabaseowner 'sa' but the user I'm running the sproc as doesnt seam to have rights to that even though I've granted execute on it for that user.

Any Ideas how to get around this?

share|improve this question
1  
Please show the relevant pieces of your stored procedure and the privileges of the user you run the SP with. –  dezso Dec 1 '12 at 23:12

1 Answer 1

up vote 2 down vote accepted

The reason for this is that when a database is created by someone who isn't a member of the sysadmin fixed server role their account is setup as the database owner. Your user will need to be granted the rights to change the database owner to another owner. However this then creates a new problem, because that user isn't a member of the database owners group any more you'll now need that user to be a member of the security admin fixed server role so that it has the rights to change the permissions within the database.

The better option hear would be to have the customer (or whoever is installing the software) to be a member of the sysadmin fixed server role while the software is being installed, or at least require that they are using a different account than the applications account and that the account has database create and security admin permissions at the instance level.

share|improve this answer
    
Thank you. I need to talk with the company owners and the director of development to see if this will work. Are there any security pitfalls to scripting this as a backup and restore? –  Lumpy Dec 3 '12 at 12:41
    
It is pretty standard to deploy applications that way. Some clients may not like it, however it would be pretty hard to cause much of a problem via a database backup being restored. –  mrdenny Dec 5 '12 at 2:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.