April 10, 2011 at 3:39 am
Will a database become not accessible or problem if the database creator (owner) quit the job and his AD account is disabled or removed?
How about other database objects?
If so, what should we do?
Also should we always use sa account to create the database? I see our sa account is disabled in most of our dbs, shall I enable it for this purpose?
Thanks
April 10, 2011 at 4:20 am
The DB will be accessible, but some objects will break. No need to use the sa, leave it disabled. Just change the DB's owner to sa after the DB is created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2011 at 4:52 am
annasql (4/10/2011)
If so, what should we do?
Quite often the database properties will be become inaccessible so use the stored procedure
sp_ChangeDBOwner to change the ownership of the database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2011 at 9:54 am
The database will be accessible but if any jobs defined with that owner will not run and you need to change the owner name of those jobs and its always advisable to have the owner name as "SA" or the service ID(Domain Account) you are using
April 10, 2011 at 10:20 am
Thank you all.
So even the sa account is disabled, I can still change the job owner or database owner to sa account, is that correct?
If so, just curious how come an account is disabled -in this case sa account is disabled, all its objects still work
?
Isn't it a good practice to leave sa disabled?
Thanks
April 10, 2011 at 11:20 am
Because nothing needs to log in via the database owner, hence the status is irrelevant. The account must just exist, which is why there are problems if the job/db owner is a domain account and that account is removed (disabled is fine, removed is not).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply