October 17, 2007 at 6:42 am
Firstly, let me say at the outset that I'm a novice SQL user and have only dabbled as much as I have to with the many intricacies of MSSQL. Apologies for my ignorance !
I have a simple installation of SQL Server Express 2005 (the 'new' MSDE) hosting a database to which various clients connect. Everything is working fine with the database and log files in their default locations on the C: drive, as created by the SQL install.
Trouble is, I need the database files to reside on a dfferent disk volume. When I try to create a new DB or backup there, SQL Server Management Studio can't see (or doesn't show) that disk.
The disk in question has restricted user permissions (ie Everyone does not have Full Control on it), and I guess the problem is somehow caused by the more complex security roles in 2005, since I didn't have this issue in 2000. If I look at the disk's access Permissions in WinExplorer, the Administrators group has Full Control, but I see that there are also a handful of SQL-related groups.
Can somebody summarise (or point me to a summary of) which principals, groups or user accounts need to be granted what rights to the folder in order for SQL2005 to be able to operate a database in it ?
Thanks in advance.
October 17, 2007 at 7:36 pm
Here is what I see. This is with the Developer Edition. Your groups may be named differently depending on instance names, etc. For Express, these should all be security groups local to your server. SQL Server 2005 now uses the groups to handle security permissions because it allows for the changing out of a service account without lingering permissions.
Assuming C:\Program Files\Microsoft SQL Server
SubFolder:
MSSQL - SQLServer2005MSSQLUser (Read & Execute)
MSSQL\Backup - SQLServer2005MSSQLUser (Full Control)
MSSQL\Binn - SQLServer2005MSSQLUser (Full Control); SQLServer2005MSFTEUser (Read & Execute); SQLServer2005SQLAgentUser (Read & Execute)
MSSQL\Data - SQLServer2005MSSQLUser (Full Control)
MSSQL\FTData - SQLServer2005MSSQLUser (Full Control); SQLServer2005MSFTEUser (Full Control)
MSSQL\Install - SQLServer2005MSSQLUser (Read & Execute); SQLServer2005MSFTEUser (Read & Execute)
MSSQL\JOBS - SQLServer2005MSSQLUser (Read & Execute); SQLServer2005SQLAgentUser (Full Control)
MSSQL\LOG - SQLServer2005MSSQLUser (Full Control); SQLServer2005MSFTEUser (Full Control); SQLServer2005SQLAgentUser (Full Control)
MSSQL\repldata - SQLServer2005MSSQLUser (Full Control);
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply