Tuesday, March 20, 2012

access 2003 to SQL server migration

We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures
1. You can make them a member of the datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production server anyway.
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrote in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures
|||makes sense on #2 i jsut wanted to rule it out because i'm sure its going to get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:%23JvZHzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production server anyway.
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrote in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures

No comments:

Post a Comment