moving databases to different directory -- preserving permission

Last Post 27 Feb 2012 02:39 PM by gunneyk. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ctseah
New Member
New Member

--
27 Jan 2012 12:59 AM
Hi,

I need to move the mdf and ldf files of the application databases to a different location.

I intend to use detach the databases, move the mdf and ldf files to another location and attach them.

All these will be done through via the SQL studio management.

I wonder whether my permission will be retained after attaching the database ?
Which account should I use to detach and attach the database ?

Thanks in advance.

Regards
rm
New Member
New Member

--
27 Jan 2012 05:55 AM
Will not affect permission if just relocation the db on same instance, otherwise you have to copy related sql logins to target instance. If in same instance, you can change file path with 'alter database modify file ...', take db offline, move files to new folder then bring db online. I prefer do backup/restore if move to another instance, much safer.
ctseah
New Member
New Member

--
27 Jan 2012 11:31 PM

Hi ,

Noted with thanks.

I am moving the databases in the same instance.

May I know whether I should us 'sa' to move the database using detach and attach method or use the owner of the database account ? The owner of the database account is a window account.

Regards

 

 

rm
New Member
New Member

--
28 Jan 2012 11:28 AM
I'll use sa because attaching db needs dbcreator rights.
russellb
New Member
New Member

--
30 Jan 2012 03:18 PM
Or, if your windows account has sysadmin permissions that'll be fine.
ctseah
New Member
New Member

--
07 Feb 2012 06:08 PM
Hi,

While attaching, it will default the owner of the database to be the user account that is used to perform the attached.
However I can always change the default owner, thus not much issue on this.

Any suggestion what is the best practise for the owner of the database to be ?
Should the owner of the database be "sa" ? Or it is alright to be some other account ?
Any best practise on this portion ?

Thanks
rm
New Member
New Member

--
08 Feb 2012 06:08 AM
Owner can be any sql id, depends on app config. I know lot of third party apps have their owner sql login and set it as db owner.
gunneyk
New Member
New Member

--
08 Feb 2012 07:48 AM
The best practice is to make the db owner sa so that there is never an issue with any particular users account getting disabled such as is the case when they leave the company.
ctseah
New Member
New Member

--
25 Feb 2012 10:32 PM

Hi,

Anybody know why I am not able to create a new post ?
I am not able to type in anything at the "body" section.

regards
russellb
New Member
New Member

--
26 Feb 2012 07:17 AM
That's strange. Works for me using IE9 x64. What browser are you using?
gunneyk
New Member
New Member

--
27 Feb 2012 02:39 PM
Works for me too.
You are not authorized to post a reply.

Acceptable Use Policy