2

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?

Improve this question
1
  • 1
    Please show the relevant pieces of your stored procedure and the privileges of the user you run the SP with.
    – András Váczi
    Dec 1, 2012 at 23:12

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • 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, 2012 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, 2012 at 2:14
0

I experienced the same error message but for a different reason. After migrating a database to another box (OS upgrade), some SQL accounts were orphaned (SID mismatch I believe, this wouldn't happen with domain accounts).

Running the following query allowed me to change membership as I should be able to (server admin):

ALTER USER <database_user> WITH LOGIN = <sql_server_login>

Before executing the query above, you can verify that your account is indeed orphaned by running the following query:

sp_change_users_login 'Report'

OR

select DB_NAME() [database], name as [user_name], type_desc,default_schema_name,create_date,modify_date from sys.database_principals 
where type in ('G','S','U') 
and authentication_type<>2 -- Use this filter only if you are running on SQL Server 2012 and major versions and you have "contained databases"
and [sid] not in ( select [sid] from sys.server_principals where type in ('G','S','U') ) 
and name not in ('dbo','guest','INFORMATION_SCHEMA','sys','MS_DataCollectorInternalUser')
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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