I run several DTS Packages that load databases residing on two seperate
instances of SQL Server 2000 on the same server. For security reasons, I
need to be able to set the Databases on one instance to Read/Write access,
set the database access on the other instance to Read only, run the packages
on the first instance, and then reset the access to Read Only. The ideal
solution would be writing the logic in T-SQL in a stored procedure that is
already part of the Package or add a new stored procedure to the package. I
have not been able to find any reference to setting the Database access in
code. I would appreciate and welcome any advise on how to accomplish this
programming task. Thank you.
grp
Have a look at sp_dboption in bol.
You might be better off creating roles with the correct permissions and
moving the user between those roles.
"PGene2" wrote:
> I run several DTS Packages that load databases residing on two seperate
> instances of SQL Server 2000 on the same server. For security reasons, I
> need to be able to set the Databases on one instance to Read/Write access,
> set the database access on the other instance to Read only, run the packages
> on the first instance, and then reset the access to Read Only. The ideal
> solution would be writing the logic in T-SQL in a stored procedure that is
> already part of the Package or add a new stored procedure to the package. I
> have not been able to find any reference to setting the Database access in
> code. I would appreciate and welcome any advise on how to accomplish this
> programming task. Thank you.
> --
> grp
|||Thanks. This at least gives me a couple of directions to go in. I appreciate
the help.
grp
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Have a look at sp_dboption in bol.
> You might be better off creating roles with the correct permissions and
> moving the user between those roles.
>
> "PGene2" wrote:
Showing posts with label residing. Show all posts
Showing posts with label residing. Show all posts
Thursday, March 8, 2012
Wednesday, March 7, 2012
Database Access
I run several DTS Packages that load databases residing on two seperate
instances of SQL Server 2000 on the same server. For security reasons, I
need to be able to set the Databases on one instance to Read/Write access,
set the database access on the other instance to Read only, run the packages
on the first instance, and then reset the access to Read Only. The ideal
solution would be writing the logic in T-SQL in a stored procedure that is
already part of the Package or add a new stored procedure to the package. I
have not been able to find any reference to setting the Database access in
code. I would appreciate and welcome any advise on how to accomplish this
programming task. Thank you.
grpHave a look at sp_dboption in bol.
You might be better off creating roles with the correct permissions and
moving the user between those roles.
"PGene2" wrote:
> I run several DTS Packages that load databases residing on two seperate
> instances of SQL Server 2000 on the same server. For security reasons, I
> need to be able to set the Databases on one instance to Read/Write access,
> set the database access on the other instance to Read only, run the packag
es
> on the first instance, and then reset the access to Read Only. The ideal
> solution would be writing the logic in T-SQL in a stored procedure that is
> already part of the Package or add a new stored procedure to the package.
I
> have not been able to find any reference to setting the Database access in
> code. I would appreciate and welcome any advise on how to accomplish this
> programming task. Thank you.
> --
> grp|||Thanks. This at least gives me a couple of directions to go in. I appreciat
e
the help.
--
grp
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Have a look at sp_dboption in bol.
> You might be better off creating roles with the correct permissions and
> moving the user between those roles.
>
> "PGene2" wrote:
>
instances of SQL Server 2000 on the same server. For security reasons, I
need to be able to set the Databases on one instance to Read/Write access,
set the database access on the other instance to Read only, run the packages
on the first instance, and then reset the access to Read Only. The ideal
solution would be writing the logic in T-SQL in a stored procedure that is
already part of the Package or add a new stored procedure to the package. I
have not been able to find any reference to setting the Database access in
code. I would appreciate and welcome any advise on how to accomplish this
programming task. Thank you.
grpHave a look at sp_dboption in bol.
You might be better off creating roles with the correct permissions and
moving the user between those roles.
"PGene2" wrote:
> I run several DTS Packages that load databases residing on two seperate
> instances of SQL Server 2000 on the same server. For security reasons, I
> need to be able to set the Databases on one instance to Read/Write access,
> set the database access on the other instance to Read only, run the packag
es
> on the first instance, and then reset the access to Read Only. The ideal
> solution would be writing the logic in T-SQL in a stored procedure that is
> already part of the Package or add a new stored procedure to the package.
I
> have not been able to find any reference to setting the Database access in
> code. I would appreciate and welcome any advise on how to accomplish this
> programming task. Thank you.
> --
> grp|||Thanks. This at least gives me a couple of directions to go in. I appreciat
e
the help.
--
grp
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Have a look at sp_dboption in bol.
> You might be better off creating roles with the correct permissions and
> moving the user between those roles.
>
> "PGene2" wrote:
>
Database
Hi
Currently my server harddisk is running of out space and there is a database
-Testing residing on this server. What I am thinking of rectifying the
problem is to increase the harddisk space of the server and create a new
database -Test1 on the new harddisk space. After which, I will restore a
backup of the current database - Testing onto the new database -Test1. After
the restoring, I will drop database -testing and rename database - Test1 to
testing.
Please advise whether is the above approach feasible and is there any other
suggestion to rectify this problem. Thanks
Regards
Christina
Backup-Restore-Rename is a good plan. I have used it myself in the past.
You can also detach the database, copy the data file(s), and reattach them.
Look up sp_detach_db and sp_attach_db in BOL for details.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:C9D770DF-4CE3-4262-B498-7E1886D06BEA@.microsoft.com...
> Hi
> Currently my server harddisk is running of out space and there is a
database
> -Testing residing on this server. What I am thinking of rectifying the
> problem is to increase the harddisk space of the server and create a new
> database -Test1 on the new harddisk space. After which, I will restore a
> backup of the current database - Testing onto the new database -Test1.
After
> the restoring, I will drop database -testing and rename database - Test1
to
> testing.
> Please advise whether is the above approach feasible and is there any
other
> suggestion to rectify this problem. Thanks
> Regards
> Christina
|||Chris,
Geoff's approach using detach/attach is probably better than
backup/restore as it will result in less down time. Just detach, copy,
attach. You don't have to wait for the restore process to happen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Geoff N. Hiten wrote:
> Backup-Restore-Rename is a good plan. I have used it myself in the past.
> You can also detach the database, copy the data file(s), and reattach them.
> Look up sp_detach_db and sp_attach_db in BOL for details.
>
|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8uvesD3EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Chris,
> Geoff's approach using detach/attach is probably better than
> backup/restore as it will result in less down time. Just detach, copy,
> attach. You don't have to wait for the restore process to happen.
I could also argue the opposite.
Imagine a 50Gig database. That's going to take a very long time to copy
between drives.
With backup/restore, you can backup from drive A, restore to drive B with a
different name.
Then stop production, do an incremental backup restore.
Rename original copy. Rename new copy to original name.
Start production.
So, I'd say "it all depends."
[vbcol=seagreen]
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Geoff N. Hiten wrote:
past.[vbcol=seagreen]
them.[vbcol=seagreen]
Currently my server harddisk is running of out space and there is a database
-Testing residing on this server. What I am thinking of rectifying the
problem is to increase the harddisk space of the server and create a new
database -Test1 on the new harddisk space. After which, I will restore a
backup of the current database - Testing onto the new database -Test1. After
the restoring, I will drop database -testing and rename database - Test1 to
testing.
Please advise whether is the above approach feasible and is there any other
suggestion to rectify this problem. Thanks
Regards
Christina
Backup-Restore-Rename is a good plan. I have used it myself in the past.
You can also detach the database, copy the data file(s), and reattach them.
Look up sp_detach_db and sp_attach_db in BOL for details.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:C9D770DF-4CE3-4262-B498-7E1886D06BEA@.microsoft.com...
> Hi
> Currently my server harddisk is running of out space and there is a
database
> -Testing residing on this server. What I am thinking of rectifying the
> problem is to increase the harddisk space of the server and create a new
> database -Test1 on the new harddisk space. After which, I will restore a
> backup of the current database - Testing onto the new database -Test1.
After
> the restoring, I will drop database -testing and rename database - Test1
to
> testing.
> Please advise whether is the above approach feasible and is there any
other
> suggestion to rectify this problem. Thanks
> Regards
> Christina
|||Chris,
Geoff's approach using detach/attach is probably better than
backup/restore as it will result in less down time. Just detach, copy,
attach. You don't have to wait for the restore process to happen.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Geoff N. Hiten wrote:
> Backup-Restore-Rename is a good plan. I have used it myself in the past.
> You can also detach the database, copy the data file(s), and reattach them.
> Look up sp_detach_db and sp_attach_db in BOL for details.
>
|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8uvesD3EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Chris,
> Geoff's approach using detach/attach is probably better than
> backup/restore as it will result in less down time. Just detach, copy,
> attach. You don't have to wait for the restore process to happen.
I could also argue the opposite.
Imagine a 50Gig database. That's going to take a very long time to copy
between drives.
With backup/restore, you can backup from drive A, restore to drive B with a
different name.
Then stop production, do an incremental backup restore.
Rename original copy. Rename new copy to original name.
Start production.
So, I'd say "it all depends."
[vbcol=seagreen]
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Geoff N. Hiten wrote:
past.[vbcol=seagreen]
them.[vbcol=seagreen]
Database
Hi
Currently my server harddisk is running of out space and there is a database
-Testing residing on this server. What I am thinking of rectifying the
problem is to increase the harddisk space of the server and create a new
database -Test1 on the new harddisk space. After which, I will restore a
backup of the current database - Testing onto the new database -Test1. After
the restoring, I will drop database -testing and rename database - Test1 to
testing.
Please advise whether is the above approach feasible and is there any other
suggestion to rectify this problem. Thanks
Regards
ChristinaBackup-Restore-Rename is a good plan. I have used it myself in the past.
You can also detach the database, copy the data file(s), and reattach them.
Look up sp_detach_db and sp_attach_db in BOL for details.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:C9D770DF-4CE3-4262-B498-7E1886D06BEA@.microsoft.com...
> Hi
> Currently my server harddisk is running of out space and there is a
database
> -Testing residing on this server. What I am thinking of rectifying the
> problem is to increase the harddisk space of the server and create a new
> database -Test1 on the new harddisk space. After which, I will restore a
> backup of the current database - Testing onto the new database -Test1.
After
> the restoring, I will drop database -testing and rename database - Test1
to
> testing.
> Please advise whether is the above approach feasible and is there any
other
> suggestion to rectify this problem. Thanks
> Regards
> Christina|||Chris,
Geoff's approach using detach/attach is probably better than
backup/restore as it will result in less down time. Just detach, copy,
attach. You don't have to wait for the restore process to happen.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Geoff N. Hiten wrote:
> Backup-Restore-Rename is a good plan. I have used it myself in the past.
> You can also detach the database, copy the data file(s), and reattach them.
> Look up sp_detach_db and sp_attach_db in BOL for details.
>|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8uvesD3EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Chris,
> Geoff's approach using detach/attach is probably better than
> backup/restore as it will result in less down time. Just detach, copy,
> attach. You don't have to wait for the restore process to happen.
I could also argue the opposite.
Imagine a 50Gig database. That's going to take a very long time to copy
between drives.
With backup/restore, you can backup from drive A, restore to drive B with a
different name.
Then stop production, do an incremental backup restore.
Rename original copy. Rename new copy to original name.
Start production.
So, I'd say "it all depends."
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Geoff N. Hiten wrote:
> > Backup-Restore-Rename is a good plan. I have used it myself in the
past.
> > You can also detach the database, copy the data file(s), and reattach
them.
> > Look up sp_detach_db and sp_attach_db in BOL for details.
> >
Currently my server harddisk is running of out space and there is a database
-Testing residing on this server. What I am thinking of rectifying the
problem is to increase the harddisk space of the server and create a new
database -Test1 on the new harddisk space. After which, I will restore a
backup of the current database - Testing onto the new database -Test1. After
the restoring, I will drop database -testing and rename database - Test1 to
testing.
Please advise whether is the above approach feasible and is there any other
suggestion to rectify this problem. Thanks
Regards
ChristinaBackup-Restore-Rename is a good plan. I have used it myself in the past.
You can also detach the database, copy the data file(s), and reattach them.
Look up sp_detach_db and sp_attach_db in BOL for details.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:C9D770DF-4CE3-4262-B498-7E1886D06BEA@.microsoft.com...
> Hi
> Currently my server harddisk is running of out space and there is a
database
> -Testing residing on this server. What I am thinking of rectifying the
> problem is to increase the harddisk space of the server and create a new
> database -Test1 on the new harddisk space. After which, I will restore a
> backup of the current database - Testing onto the new database -Test1.
After
> the restoring, I will drop database -testing and rename database - Test1
to
> testing.
> Please advise whether is the above approach feasible and is there any
other
> suggestion to rectify this problem. Thanks
> Regards
> Christina|||Chris,
Geoff's approach using detach/attach is probably better than
backup/restore as it will result in less down time. Just detach, copy,
attach. You don't have to wait for the restore process to happen.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Geoff N. Hiten wrote:
> Backup-Restore-Rename is a good plan. I have used it myself in the past.
> You can also detach the database, copy the data file(s), and reattach them.
> Look up sp_detach_db and sp_attach_db in BOL for details.
>|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:O8uvesD3EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Chris,
> Geoff's approach using detach/attach is probably better than
> backup/restore as it will result in less down time. Just detach, copy,
> attach. You don't have to wait for the restore process to happen.
I could also argue the opposite.
Imagine a 50Gig database. That's going to take a very long time to copy
between drives.
With backup/restore, you can backup from drive A, restore to drive B with a
different name.
Then stop production, do an incremental backup restore.
Rename original copy. Rename new copy to original name.
Start production.
So, I'd say "it all depends."
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Geoff N. Hiten wrote:
> > Backup-Restore-Rename is a good plan. I have used it myself in the
past.
> > You can also detach the database, copy the data file(s), and reattach
them.
> > Look up sp_detach_db and sp_attach_db in BOL for details.
> >
Subscribe to:
Posts (Atom)