Detach and attaching changing file permissions

Hi,
We are running Sql Server 2005. We have the mdf file permissions including
Administrator and Authenticated Users. After a detach, copy of the mdf file
by a VB6 exe program running from a computer as an Authenticated User, then
a attach of the mdf file, the Authenticated Users group gets removed from
the individual mdf file permissions. Any help would be greatly appreciated.
Thanks Ellie
I've just found out that the Sql Server does not have an Authenticated Users
as one of its groups. Could this be a problem?
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||Hi Ellie,
The change in permissions on the mdf file after detaching and attaching is
by design in SQL Server 2005. This Books Online topic explains what
happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We are running Sql Server 2005. We have the mdf file permissions including
> Administrator and Authenticated Users. After a detach, copy of the mdf
> file by a VB6 exe program running from a computer as an Authenticated
> User, then a attach of the mdf file, the Authenticated Users group gets
> removed from the individual mdf file permissions. Any help would be
> greatly appreciated.
> Thanks Ellie
>
|||So if the mdf file, after detaching, has a user group added to it,
Authenticated Users, but that group is not in the sql server groups, then it
would remove that group upon re-attach? If we added the Auth Users to the
Sql Server, would we be ok?
Thanks for your help,
Ellie
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
> Hi Ellie,
> The change in permissions on the mdf file after detaching and attaching is
> by design in SQL Server 2005. This Books Online topic explains what
> happens: http://msdn2.microsoft.com/en-us/library/ms189128.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Ellie" <nospam@.nospam.net> wrote in message
> news:%23bsFo8qNIHA.292@.TK2MSFTNGP02.phx.gbl...
>
|||> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach?
Correct. When a database is detached, the permissions on the file are
restricted to the user that executed the detach statement. When the
database is reattached, the file permissions are set to the SQL Server
(MSSQLSERVER) service account and members of the local Windows
Administrators group.

> If we added the Auth Users to the Sql Server, would we be ok?
To do this, you would need to add the Authenticated Users group to the
Windows group that maps to the MSSQLSERVER service account. This is
typically SQLServer2005MSSQLUser$<computer_name>MSSQLSERVER. However, when
you do this, you're giving this group access to ALL the SQL Server files,
registry entries, etc. We generally recommend that you restrict the ability
to perform tasks like creating and dropping databases, detach/attach, etc.
to a limited number of logins, but I don't understand your business needs,
so this may or may not be acceptable.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Ellie" <nospam@.nospam.net> wrote in message
news:uHP5xv0NIHA.5988@.TK2MSFTNGP02.phx.gbl...
> So if the mdf file, after detaching, has a user group added to it,
> Authenticated Users, but that group is not in the sql server groups, then
> it would remove that group upon re-attach? If we added the Auth Users to
> the Sql Server, would we be ok?
> Thanks for your help,
> Ellie
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:e5mqvttNIHA.4912@.TK2MSFTNGP06.phx.gbl...
>