April 30, 2008 at 8:59 am
I have to restore several databases each day. The restore process is driven by a web app. The login (L1) who executes the restores can not have sysadmin privileges, so each restored db is owned by L1. I want the databases to be owned by SA. I have tried
SP_CHANGEDBOWNER 'SA'
Cannot find the principal 'SA', because it does not exist or you do not have permission.
GRANT TAKE OWNERSHIP ON DATABASE::db_1 to SA
Cannot find the user 'SA', because it does not exist or you do not have permission.
ALTER AUTHORIZATION ON DATABASE::db_1 to SA
Cannot find the principal 'SA', because it does not exist or you do not have permission.
I created a proc in master using a sysadmin account and EXECUTE AS owner. The sp accepts the name of a db, build/execute an ALTER AUTHORIZATION statement for the database passed in. The SP works when run by an admin, but fails when run by L1 as follows
master..sp_changetfdbowner 'db_1'
Cannot find the database 'db_1', because it does not exist or you do not have permission.
db_1..sp_changetfdbowner 'db_1'
The server principal "sa" is not able to access the database "db_1" under the current security context.
Any ideas?
April 30, 2008 at 9:10 am
I bet that Sa is a special case and you can't allow someone to elevate their privileges to this level, even with EXECUTE AS.
What about just creating a dummy account and letting that one own the database? Does this cause issues? Create a job run by sa every day to re-assign it itself ownership?
April 30, 2008 at 9:17 am
I'm trying to avoid the job approach, but it's looking like my only option...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply