For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7=bc=AD96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
>
> > For some reason when I detach a database from my SQL 2005 server using
> > my client machine, the security of the database files changes so that
> > I am the only user with security to that file. It even removes all of
> > the Administrators that were there. If I attach the database back,
> > the security then changes back to the users that were originally there
> > and my username is gone. Any ideas as to why this is happening or if
> > it should work this way... Hide quoted text -
> - Show quoted text -|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:
>> For some reason when I detach a database from my SQL 2005 server using
>> my client machine, the security of the database files changes so that
>> I am the only user with security to that file. It even removes all of
>> the Administrators that were there. If I attach the database back,
>> the security then changes back to the users that were originally there
>> and my username is gone. Any ideas as to why this is happening or if
>> it should work this way?
>>
Showing posts with label changing. Show all posts
Showing posts with label changing. Show all posts
Sunday, March 11, 2012
Detaching a database in SQL2005 is changing the security of the files
For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?
Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bcX96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
>
>
> - Show quoted text -
|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...[vbcol=seagreen]
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?
Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bcX96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
>
>
> - Show quoted text -
|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...[vbcol=seagreen]
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:
Detaching a database in SQL2005 is changing the security of the files
For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc9
6f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7=
bc=AD96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
>
>
> - Show quoted text -
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc9
6f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7=
bc=AD96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
>
>
> - Show quoted text -
Friday, March 9, 2012
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...
>
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...
>
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 EllieI'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...
>> 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?
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...
>> 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
>>
>
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 EllieI'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...
>> 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?
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...
>> 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
>>
>
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 EllieI'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/...r/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/...r/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/...r/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...
>
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 EllieI'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/...r/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/...r/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/...r/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...
>
Wednesday, March 7, 2012
Destination resets when changing selected databases
I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
Destination resets when changing selected databases
I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
Destination resets when changing selected databases
I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.
I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).
Thanks in advance.
This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.
If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.
I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.
Thanks.
|||If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),
Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
and modify the BackupDirectory value, you'll have this as the new default.
Note that this permanently modifies the default location for ALL TSQL backups!
|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||
Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.
Thanks again for the info though.
Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.
I guess we'll just have to wait and see if it is ever acknowledged.
|||
Actually, this is a server-side setting.
If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!
|||Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.
For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.
Anyway, thanks again for the big tip! I'm sure it will help others as well.
P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.
|||It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.
Thank you for the great assistance!
|||The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.
It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.
Saturday, February 25, 2012
Designer changing report element sizes during a save
This is a repost using MSDN Subscription
Using SQL Server Reporting Services Designer Version 9.00.1399.00
I'm having trouble with a three column report with a page header the width
of the page. The designer is changing the width of elements when it is
saved. As long as you view the report in the designer, the report renders as
you would expect, but when it gets saved, some of the report elements get
changed.
Try this:
New empty Report Server Project
Add new empty report (Add -> New Item -> Select Report)
No datasource
Enable page header
Add a text box (textbox1) to page header. Make it begin at the left
margin/top margin and be almost the width of the page (6in).
Set the height of the page header to the height of the textbox1.
Insert five or six words in textbox1.
Make the report a 3 column report.
Reset the page header width to be 6.5in (1in right/left margins on 8.5in
paper) using the resizer in the page header.
The column width should be 1.83333in (1.83333 * 3 = 5.49999 + [column
spacing] 0.5in + 0.5in = 6.49999in)
Add a text box (textbox2) to the body (in column 1). Make it begin at the
left margin/top margin and be almost the width of the column (1.625in).
Insert a few words in textbox2.
View the report. Appears to be fine.
Save, Close the report, Reopen the report.
The width of the page header is now 6in (was 6.5in), column width is now
1.66667in (was 1.83333in)
Why did the width of the page header change?
Make the width of the page header and textbox1, 6.5in.
Save, Close the report, Reopen the report.
The page header is 6.499999in wide, but textbox1 is the same width as one of
the columns in the body (1.83333in)
Why did the width of the page header and textbox1 change?
Make textbox1 6in wide again.
Make the report 6.5in wide.
Add a new textbox (textbox3) in the page header to the right of textbox1
(starts at 6in, 0.5in wide), same height as the first one.
Insert a few letters in textbox3.
Save, Close the report, Reopen the report.
The report is now 6in wide (instead of 6.5in), columns are 1.66667in wide.
Textbox3 that was just added to the page header cannot be seen. It is at
the right edge of the page header, and has a negative width. It can only be
found using dropdown in the properties window.
Why did textbox3 disappear/get a negative width?
Are these known bugs? Are there any work arounds?Hello,
From your desscription, I tried to reproduce the issue to no avail. To
understand the issue better, will you please send the report project or
report to me? You could remove "online" in my displayed email address. I
know the report will be changed after saving, and reopen, however, you
report shall make me understanding your problem more directly. thank you!
Also, since you did not install SQL 2005 sp1, you may want to install it to
test the sitaution.
If you have other related information, please also let's know. We look
forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Im having the same error!! I have 2 reports that have mutliple columns. When
I save the report, it will change the text boxes in the header to negative
values. I have not found a work around for this error. Anyone have any
suggestions/Have run into the same problem as well?
-Theresa
"AOTX San Antonio" wrote:
> This is a repost using MSDN Subscription
> Using SQL Server Reporting Services Designer Version 9.00.1399.00
> I'm having trouble with a three column report with a page header the width
> of the page. The designer is changing the width of elements when it is
> saved. As long as you view the report in the designer, the report renders as
> you would expect, but when it gets saved, some of the report elements get
> changed.
> Try this:
> New empty Report Server Project
> Add new empty report (Add -> New Item -> Select Report)
> No datasource
> Enable page header
> Add a text box (textbox1) to page header. Make it begin at the left
> margin/top margin and be almost the width of the page (6in).
> Set the height of the page header to the height of the textbox1.
> Insert five or six words in textbox1.
> Make the report a 3 column report.
> Reset the page header width to be 6.5in (1in right/left margins on 8.5in
> paper) using the resizer in the page header.
> The column width should be 1.83333in (1.83333 * 3 = 5.49999 + [column
> spacing] 0.5in + 0.5in = 6.49999in)
> Add a text box (textbox2) to the body (in column 1). Make it begin at the
> left margin/top margin and be almost the width of the column (1.625in).
> Insert a few words in textbox2.
> View the report. Appears to be fine.
> Save, Close the report, Reopen the report.
> The width of the page header is now 6in (was 6.5in), column width is now
> 1.66667in (was 1.83333in)
> Why did the width of the page header change?
> Make the width of the page header and textbox1, 6.5in.
> Save, Close the report, Reopen the report.
> The page header is 6.499999in wide, but textbox1 is the same width as one of
> the columns in the body (1.83333in)
> Why did the width of the page header and textbox1 change?
> Make textbox1 6in wide again.
> Make the report 6.5in wide.
> Add a new textbox (textbox3) in the page header to the right of textbox1
> (starts at 6in, 0.5in wide), same height as the first one.
> Insert a few letters in textbox3.
> Save, Close the report, Reopen the report.
> The report is now 6in wide (instead of 6.5in), columns are 1.66667in wide.
> Textbox3 that was just added to the page header cannot be seen. It is at
> the right edge of the page header, and has a negative width. It can only be
> found using dropdown in the properties window.
> Why did textbox3 disappear/get a negative width?
> Are these known bugs? Are there any work arounds?|||Hello Theresa
,
I have reproduced the issue on my side. It seems that when adding textbox
or change textbox, it does not actually perform an optimization of the
report according to the algorithm that used when saving a report. I found
if I change the textbox1 width to 6 to 6.5, this size is not actually saved
to rdl and the width number is not stored at all. At this time, textbox1
actually has the 6.5 when rendering since it is aligned to the body width
1.83333 automatically.
Please rest assured your feedback is taken seriously this issue has been
reported to the product team. Product team may evaluate the business impact
and decide to fix this or not. If there is any update, we will let you
know.
You may manually edit rdl and set textbox in pageheader to the value it
should be to work around the issue. In the meantime, I also encourage you
submit your feedback on this issue via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Every One,
I came across this issue and found a good work around for this.
Whenever you have the textbox's right margin alligned with the right
margin of a rectangle in a Page Header, we see this behavour in a multi
column report.
Work around: We suggest to keep a minimal gap between the textbox right
edge and the container and make the container border as none. This
makes it retain the width attribute in the rdl file and we dont loose
its width after saving.
Best Regards,
Dipanjan Banik
SQL Developer Support
Peter Yang [MSFT] wrote:
> Hello Theresa
> ,
> I have reproduced the issue on my side. It seems that when adding textbox
> or change textbox, it does not actually perform an optimization of the
> report according to the algorithm that used when saving a report. I found
> if I change the textbox1 width to 6 to 6.5, this size is not actually saved
> to rdl and the width number is not stored at all. At this time, textbox1
> actually has the 6.5 when rendering since it is aligned to the body width
> 1.83333 automatically.
> Please rest assured your feedback is taken seriously this issue has been
> reported to the product team. Product team may evaluate the business impact
> and decide to fix this or not. If there is any update, we will let you
> know.
> You may manually edit rdl and set textbox in pageheader to the value it
> should be to work around the issue. In the meantime, I also encourage you
> submit your feedback on this issue via the link below
> http://lab.msdn.microsoft.com/productfeedback/default.aspx
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
Using SQL Server Reporting Services Designer Version 9.00.1399.00
I'm having trouble with a three column report with a page header the width
of the page. The designer is changing the width of elements when it is
saved. As long as you view the report in the designer, the report renders as
you would expect, but when it gets saved, some of the report elements get
changed.
Try this:
New empty Report Server Project
Add new empty report (Add -> New Item -> Select Report)
No datasource
Enable page header
Add a text box (textbox1) to page header. Make it begin at the left
margin/top margin and be almost the width of the page (6in).
Set the height of the page header to the height of the textbox1.
Insert five or six words in textbox1.
Make the report a 3 column report.
Reset the page header width to be 6.5in (1in right/left margins on 8.5in
paper) using the resizer in the page header.
The column width should be 1.83333in (1.83333 * 3 = 5.49999 + [column
spacing] 0.5in + 0.5in = 6.49999in)
Add a text box (textbox2) to the body (in column 1). Make it begin at the
left margin/top margin and be almost the width of the column (1.625in).
Insert a few words in textbox2.
View the report. Appears to be fine.
Save, Close the report, Reopen the report.
The width of the page header is now 6in (was 6.5in), column width is now
1.66667in (was 1.83333in)
Why did the width of the page header change?
Make the width of the page header and textbox1, 6.5in.
Save, Close the report, Reopen the report.
The page header is 6.499999in wide, but textbox1 is the same width as one of
the columns in the body (1.83333in)
Why did the width of the page header and textbox1 change?
Make textbox1 6in wide again.
Make the report 6.5in wide.
Add a new textbox (textbox3) in the page header to the right of textbox1
(starts at 6in, 0.5in wide), same height as the first one.
Insert a few letters in textbox3.
Save, Close the report, Reopen the report.
The report is now 6in wide (instead of 6.5in), columns are 1.66667in wide.
Textbox3 that was just added to the page header cannot be seen. It is at
the right edge of the page header, and has a negative width. It can only be
found using dropdown in the properties window.
Why did textbox3 disappear/get a negative width?
Are these known bugs? Are there any work arounds?Hello,
From your desscription, I tried to reproduce the issue to no avail. To
understand the issue better, will you please send the report project or
report to me? You could remove "online" in my displayed email address. I
know the report will be changed after saving, and reopen, however, you
report shall make me understanding your problem more directly. thank you!
Also, since you did not install SQL 2005 sp1, you may want to install it to
test the sitaution.
If you have other related information, please also let's know. We look
forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Im having the same error!! I have 2 reports that have mutliple columns. When
I save the report, it will change the text boxes in the header to negative
values. I have not found a work around for this error. Anyone have any
suggestions/Have run into the same problem as well?
-Theresa
"AOTX San Antonio" wrote:
> This is a repost using MSDN Subscription
> Using SQL Server Reporting Services Designer Version 9.00.1399.00
> I'm having trouble with a three column report with a page header the width
> of the page. The designer is changing the width of elements when it is
> saved. As long as you view the report in the designer, the report renders as
> you would expect, but when it gets saved, some of the report elements get
> changed.
> Try this:
> New empty Report Server Project
> Add new empty report (Add -> New Item -> Select Report)
> No datasource
> Enable page header
> Add a text box (textbox1) to page header. Make it begin at the left
> margin/top margin and be almost the width of the page (6in).
> Set the height of the page header to the height of the textbox1.
> Insert five or six words in textbox1.
> Make the report a 3 column report.
> Reset the page header width to be 6.5in (1in right/left margins on 8.5in
> paper) using the resizer in the page header.
> The column width should be 1.83333in (1.83333 * 3 = 5.49999 + [column
> spacing] 0.5in + 0.5in = 6.49999in)
> Add a text box (textbox2) to the body (in column 1). Make it begin at the
> left margin/top margin and be almost the width of the column (1.625in).
> Insert a few words in textbox2.
> View the report. Appears to be fine.
> Save, Close the report, Reopen the report.
> The width of the page header is now 6in (was 6.5in), column width is now
> 1.66667in (was 1.83333in)
> Why did the width of the page header change?
> Make the width of the page header and textbox1, 6.5in.
> Save, Close the report, Reopen the report.
> The page header is 6.499999in wide, but textbox1 is the same width as one of
> the columns in the body (1.83333in)
> Why did the width of the page header and textbox1 change?
> Make textbox1 6in wide again.
> Make the report 6.5in wide.
> Add a new textbox (textbox3) in the page header to the right of textbox1
> (starts at 6in, 0.5in wide), same height as the first one.
> Insert a few letters in textbox3.
> Save, Close the report, Reopen the report.
> The report is now 6in wide (instead of 6.5in), columns are 1.66667in wide.
> Textbox3 that was just added to the page header cannot be seen. It is at
> the right edge of the page header, and has a negative width. It can only be
> found using dropdown in the properties window.
> Why did textbox3 disappear/get a negative width?
> Are these known bugs? Are there any work arounds?|||Hello Theresa
,
I have reproduced the issue on my side. It seems that when adding textbox
or change textbox, it does not actually perform an optimization of the
report according to the algorithm that used when saving a report. I found
if I change the textbox1 width to 6 to 6.5, this size is not actually saved
to rdl and the width number is not stored at all. At this time, textbox1
actually has the 6.5 when rendering since it is aligned to the body width
1.83333 automatically.
Please rest assured your feedback is taken seriously this issue has been
reported to the product team. Product team may evaluate the business impact
and decide to fix this or not. If there is any update, we will let you
know.
You may manually edit rdl and set textbox in pageheader to the value it
should be to work around the issue. In the meantime, I also encourage you
submit your feedback on this issue via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Every One,
I came across this issue and found a good work around for this.
Whenever you have the textbox's right margin alligned with the right
margin of a rectangle in a Page Header, we see this behavour in a multi
column report.
Work around: We suggest to keep a minimal gap between the textbox right
edge and the container and make the container border as none. This
makes it retain the width attribute in the rdl file and we dont loose
its width after saving.
Best Regards,
Dipanjan Banik
SQL Developer Support
Peter Yang [MSFT] wrote:
> Hello Theresa
> ,
> I have reproduced the issue on my side. It seems that when adding textbox
> or change textbox, it does not actually perform an optimization of the
> report according to the algorithm that used when saving a report. I found
> if I change the textbox1 width to 6 to 6.5, this size is not actually saved
> to rdl and the width number is not stored at all. At this time, textbox1
> actually has the 6.5 when rendering since it is aligned to the body width
> 1.83333 automatically.
> Please rest assured your feedback is taken seriously this issue has been
> reported to the product team. Product team may evaluate the business impact
> and decide to fix this or not. If there is any update, we will let you
> know.
> You may manually edit rdl and set textbox in pageheader to the value it
> should be to work around the issue. In the meantime, I also encourage you
> submit your feedback on this issue via the link below
> http://lab.msdn.microsoft.com/productfeedback/default.aspx
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
Subscribe to:
Posts (Atom)