Determining Security: attaching

Showing posts with label attaching. Show all posts

Sunday, March 11, 2012

detaching db and attaching to another server

Using SS2000 SP4. I've detached a db from server A and been able to attach i
t
without any problem to server B. However, if I try to attach it to server C,
the database shows as read-only. The same thing happens with other databases
that I try to attach to server C.
Any idea why?
Thanks,
--
Dan D.Dan D. wrote:
> Using SS2000 SP4. I've detached a db from server A and been able to attach
it
> without any problem to server B. However, if I try to attach it to server
C,
> the database shows as read-only. The same thing happens with other databas
es
> that I try to attach to server C.
> Any idea why?
> Thanks,
> --
> Dan D.
Check to see that you are not putting your database data files in a
read only folder. I had read only to true by accident and got the same
symptoms you did. When you turn off read only, you will have to
re-attach.
Tony|||That was it. Thanks. Do you know what the minimum permissions are? I gave
full control to the user that the services are using but if the user doesn't
need full control, I'd rather reduce them.
--
Dan D.
"Tony" wrote:

> Dan D. wrote:
> Check to see that you are not putting your database data files in a
> read only folder. I had read only to true by accident and got the same
> symptoms you did. When you turn off read only, you will have to
> re-attach.
> Tony
>

detaching db and attaching to another server

Using SS2000 SP4. I've detached a db from server A and been able to attach it
without any problem to server B. However, if I try to attach it to server C,
the database shows as read-only. The same thing happens with other databases
that I try to attach to server C.
Any idea why?
Thanks,
--
Dan D.Dan D. wrote:
> Using SS2000 SP4. I've detached a db from server A and been able to attach it
> without any problem to server B. However, if I try to attach it to server C,
> the database shows as read-only. The same thing happens with other databases
> that I try to attach to server C.
> Any idea why?
> Thanks,
> --
> Dan D.
Check to see that you are not putting your database data files in a
read only folder. I had read only to true by accident and got the same
symptoms you did. When you turn off read only, you will have to
re-attach.
Tony|||That was it. Thanks. Do you know what the minimum permissions are? I gave
full control to the user that the services are using but if the user doesn't
need full control, I'd rather reduce them.
--
Dan D.
"Tony" wrote:
> Dan D. wrote:
> > Using SS2000 SP4. I've detached a db from server A and been able to attach it
> > without any problem to server B. However, if I try to attach it to server C,
> > the database shows as read-only. The same thing happens with other databases
> > that I try to attach to server C.
> >
> > Any idea why?
> >
> > Thanks,
> > --
> > Dan D.
> Check to see that you are not putting your database data files in a
> read only folder. I had read only to true by accident and got the same
> symptoms you did. When you turn off read only, you will have to
> re-attach.
> Tony
>

Detaching and Attaching Standby Database

I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> > I need to be able to detach my standby database so I can back it up.
> > How do I reattach it and keep it from going through a recover (thus
> > breaking my log consistency with the production database)?
> >
> > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > START MSSQLSERVER, but I'm not sure if that will work.
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
> >
> > "Brad" <brad@.seesigifthere.com> wrote in message
> > news:MPG.1b7bedee1a217f72989689@.news...
> > > I need to be able to detach my standby database so I can back it up.
> > > How do I reattach it and keep it from going through a recover (thus
> > > breaking my log consistency with the production database)?
> > >
> > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > START MSSQLSERVER, but I'm not sure if that will work.
> >
> > Have you read the article in the BOL
> > "How to set up, maintain, and bring online a standby server
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> > In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> > says...
> > >
> > > "Brad" <brad@.seesigifthere.com> wrote in message
> > > news:MPG.1b7bedee1a217f72989689@.news...
> > > > I need to be able to detach my standby database so I can back it up.
> > > > How do I reattach it and keep it from going through a recover (thus
> > > > breaking my log consistency with the production database)?
> > > >
> > > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > > START MSSQLSERVER, but I'm not sure if that will work.
> > >
> > > Have you read the article in the BOL
> > > "How to set up, maintain, and bring online a standby server
> (Transact-SQL)"?
> >
> > Yes, I've read that and I have all of that working. The problem is that
> > I really want to just keep restoring logs on this server, but I want to
> > have a local backup of the standby database (45GB production database is
> > on the west coast and I am on the east with only a T1 between the two).
> > Yesterday I had some kind of disk hiccup during a log restore and the
> > standby database was hosed so I have to go back to a 1.5 week old full
> > backup and a ton of log restores. If I could just automatically backup
> > (full) the standby database each night my restore issue would be much
> > smaller. I understand that I can't do an actual backup, but I should be
> > able to detach, copy and attach or something like that.
> I see what you mean
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
But I can't do real log shipping because I am on standard.

Detaching and Attaching DB;s

I'm trying to detach a DB and then attach it with the
data and log files in a different location.
Here's what I'm doing.
I detach the DB in question
Copy the data and log files to a different location
Attach DB, using the copied files.
It attaches fine, but it makes the DB Read-Only, and I'm
unable to uncheck the read-only option in the properties
of the db. It gives a long error about the possibiliy of
the data and log files not being correct, which I know
they are.
Any thoughts?
Thanks much
MattSuggest running DBCC CHECKDB on the source, if good try detaching again
--
HTH
Ryan Waight, MCDBA, MCSE
"Matt Bender" <matt.bender@.berbee.com> wrote in message
news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> I'm trying to detach a DB and then attach it with the
> data and log files in a different location.
> Here's what I'm doing.
> I detach the DB in question
> Copy the data and log files to a different location
> Attach DB, using the copied files.
> It attaches fine, but it makes the DB Read-Only, and I'm
> unable to uncheck the read-only option in the properties
> of the db. It gives a long error about the possibiliy of
> the data and log files not being correct, which I know
> they are.
> Any thoughts?
> Thanks much
> Matt|||Hi Matt
Is it possible the files themselves are read only in the OS? Maybe the
folder you copied them to had its readonly property set, and the db files
inherited that.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Bender" <matt.bender@.berbee.com> wrote in message
news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> I'm trying to detach a DB and then attach it with the
> data and log files in a different location.
> Here's what I'm doing.
> I detach the DB in question
> Copy the data and log files to a different location
> Attach DB, using the copied files.
> It attaches fine, but it makes the DB Read-Only, and I'm
> unable to uncheck the read-only option in the properties
> of the db. It gives a long error about the possibiliy of
> the data and log files not being correct, which I know
> they are.
> Any thoughts?
> Thanks much
> Matt|||thanks for the response. I did check that as well and
that is not the case either.
matt
>--Original Message--
>Hi Matt
>Is it possible the files themselves are read only in the
OS? Maybe the
>folder you copied them to had its readonly property set,
and the db files
>inherited that.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Matt Bender" <matt.bender@.berbee.com> wrote in message
>news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
>> I'm trying to detach a DB and then attach it with the
>> data and log files in a different location.
>> Here's what I'm doing.
>> I detach the DB in question
>> Copy the data and log files to a different location
>> Attach DB, using the copied files.
>> It attaches fine, but it makes the DB Read-Only, and
I'm
>> unable to uncheck the read-only option in the
properties
>> of the db. It gives a long error about the possibiliy
of
>> the data and log files not being correct, which I know
>> they are.
>> Any thoughts?
>> Thanks much
>> Matt
>
>.
>|||Are you sure no one is in the database? What happens when you try to set the
db to not read-only using ALTER DATABASE WITH ROLLBACK...
(Please see BOL for full syntax details.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matt Bender" <anonymous@.discussions.microsoft.com> wrote in message
news:06d601c3b2cd$4df5cca0$a101280a@.phx.gbl...
> thanks for the response. I did check that as well and
> that is not the case either.
> matt
> >--Original Message--
> >Hi Matt
> >
> >Is it possible the files themselves are read only in the
> OS? Maybe the
> >folder you copied them to had its readonly property set,
> and the db files
> >inherited that.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Matt Bender" <matt.bender@.berbee.com> wrote in message
> >news:093c01c3b2a8$d108f1a0$a001280a@.phx.gbl...
> >> I'm trying to detach a DB and then attach it with the
> >> data and log files in a different location.
> >>
> >> Here's what I'm doing.
> >> I detach the DB in question
> >> Copy the data and log files to a different location
> >> Attach DB, using the copied files.
> >> It attaches fine, but it makes the DB Read-Only, and
> I'm
> >> unable to uncheck the read-only option in the
> properties
> >> of the db. It gives a long error about the possibiliy
> of
> >> the data and log files not being correct, which I know
> >> they are.
> >>
> >> Any thoughts?
> >>
> >> Thanks much
> >> Matt
> >
> >
> >.
> >

Detaching and Attaching a database

Hi:
I need to detach the databse to change its location and then attach again.
If this database is a publisher in a replication, will detaching and
attaching affect anything? I don't think so but i just want to confirm it.
Thanks
You must remove all publications from the database and unpublish the
database to detach it.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:98E2311A-E92D-4041-ABDC-0ABA37C39D78@.microsoft.com...
> Hi:
> I need to detach the databse to change its location and then attach again.
> If this database is a publisher in a replication, will detaching and
> attaching affect anything? I don't think so but i just want to confirm
> it.
> Thanks
|||Thanks for replying. Can you please tell me how do I remove all publications
from the database and unpublish the database it? And after I attach it, how
do I put all all the publications back and publish the database again. Can
you please give me some instructions? Actually I am new to replication.
Thanks
Sohail
"Hilary Cotter" wrote:

> You must remove all publications from the database and unpublish the
> database to detach it.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:98E2311A-E92D-4041-ABDC-0ABA37C39D78@.microsoft.com...
>
>

Detaching & Attaching Database

Hello Group.
I have the following problem using SQL Server 2000 on WinXP machine.
I detached a SQL database and then performed a windows copy of the database
to another folder on the system.
I then renamed the database and tried to attach the renamed database to SQL.
SQL sees the old name and says there is already a database with that name
attached.
How can I solve this problem?
How can I get SQL to see the new name given to the MDF and LDF files?
Thanks,
Terry"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>
Renaming the MDF / LDF files has nothing to do with renaming the database.
The name for the database when it is attached is specified by the first
parameter of sp_attach_db. Example:
EXEC sp_attach_db 'new_db_name',
'd:\mssql\data\db_file_name.mdf',
'd:\mssql\data\db_file_name.ldf'
David Portas
SQL Server MVP
--|||Hi Terry
The name of the database does not have to have anything to do with the names
of the database files. So when you say you renamed 'the database' do you
mean you renamed the files you moved, or renamed the database on the server?
The original name of the database is stored with the files, however, even if
you change the name of the files. If you want to attach some database files
and give the database contained on those files another name than the one it
originally had, you have to specify that when you do the attach.
I suggest you use the command sp_attach_db, following the examples in the
Books Online. This will allow you to specify the name of the database you
are attaching, and the physical names of all the files you want to attach.
If you still are unsuccessful, you can then post the exact command you are
using and the exact error message you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>|||Okay, but how do I accomplish this with enterprise manager?
In the dialog box that appears when you go to attach the database, it gives
you the orginal database name.
In the box "attach as" I give it the new name and get the error that it
already exits.
What am I missing here?
Thanks, for your quick response.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
> Renaming the MDF / LDF files has nothing to do with renaming the database.
> The name for the database when it is attached is specified by the first
> parameter of sp_attach_db. Example:
> EXEC sp_attach_db 'new_db_name',
> 'd:\mssql\data\db_file_name.mdf',
> 'd:\mssql\data\db_file_name.ldf'
> --
> David Portas
> SQL Server MVP
> --
>|||Okay, your statement
"The original name of the database is stored with the files" explains what I
was missing.
Now I understand. I did not know this.
Thanks,
Kalen
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Edin%23KFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hi Terry
> The name of the database does not have to have anything to do with the
> names of the database files. So when you say you renamed 'the database' do
> you mean you renamed the files you moved, or renamed the database on the
> server?
> The original name of the database is stored with the files, however, even
> if you change the name of the files. If you want to attach some database
> files and give the database contained on those files another name than the
> one it originally had, you have to specify that when you do the attach.
> I suggest you use the command sp_attach_db, following the examples in the
> Books Online. This will allow you to specify the name of the database you
> are attaching, and the physical names of all the files you want to attach.
> If you still are unsuccessful, you can then post the exact command you are
> using and the exact error message you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
>
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
I don't know about accomplishing this in Enterprise Manager because I rarely
use EM that way. In my opinion it would be much easier to type the
sp_attach_db command in Query Analyzer.
David Portas
SQL Server MVP
--|||Okay,
Thanks for your help.
Much appreciated.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eEqu3XLFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> I don't know about accomplishing this in Enterprise Manager because I
> rarely use EM that way. In my opinion it would be much easier to type the
> sp_attach_db command in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>

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...
>

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
>>
>

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...
>

Sunday, March 11, 2012

detaching db and attaching to another server

detaching db and attaching to another server

Detaching and Attaching Standby Database

Detaching and Attaching Standby Database

Detaching and Attaching Standby Database

Detaching and Attaching DB;s

Detaching and Attaching a database

Detaching & Attaching Database

Friday, March 9, 2012

Detach and attaching changing file permissions

Detach and attaching changing file permissions

Detach and attaching changing file permissions

Determining Security

Blog Archive

About Me