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 security 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 stored procedure as doesn't seam to have rights to that even though I've granted execute on it for that user.
Any ideas how to get around this?