Hello all,
At work we've been using SQL Server 2000 for quite a few years. We are looking to transition to SQL Server 2008 soon, but we'll have
to move off OUR OWN server and move onto the IT folks' server.
We won't be DBAs anymore (no sa privileges). Initially forays into this arena with db_creator privileges had disastrous results.
I'm trying to figure out the least amount of privileges we need to have extended to us that will make the IT folks happy and let
us still function.
We support about 6 active database configurations. A database is generally about 300 MB with 200 or so tables. Every two weeks
we publish a database update. The updated data is a ZIP file of BCP data. Our definition of each update is a .BAK file.
We need to be able to create a new database every now and then, but db_creator doesn't seem to handle all of the requirements.
Most of the time we use three "temporary" databases: dbOLD, dbNEW and dbTrans.
DbOLD contains a restore of some old version (from a .BAK file).
DbNew contains a restore of some new version (from a .BAK file).
DbTrans contains the differences and BCPs out the data.
It appears that we can operate with no Server Roles.
We must be stipulated as DB_Owner of the above DBs.
We must run sp_ChangeDbOwner on each database.
We must have Backup/Restore privileges for each database.
And, the .BAK file must contain user definitions/privileges that allow the Restore operation.
This appears to provide a somewhat functional environment, but I'd appreciate any input relating to this topic.
Lastly, is SQL Server 2008 able to produce ANYTHING the SQL Server 2000 can digest? We still have to produce data
to support our SQL Server 2000 sites. My research hasn't indicated that that is possible.
Thanks for reading this, any responses are greatly appreciated.
Thanks,
Wayne
At work we've been using SQL Server 2000 for quite a few years. We are looking to transition to SQL Server 2008 soon, but we'll have
to move off OUR OWN server and move onto the IT folks' server.
We won't be DBAs anymore (no sa privileges). Initially forays into this arena with db_creator privileges had disastrous results.
I'm trying to figure out the least amount of privileges we need to have extended to us that will make the IT folks happy and let
us still function.
We support about 6 active database configurations. A database is generally about 300 MB with 200 or so tables. Every two weeks
we publish a database update. The updated data is a ZIP file of BCP data. Our definition of each update is a .BAK file.
We need to be able to create a new database every now and then, but db_creator doesn't seem to handle all of the requirements.
Most of the time we use three "temporary" databases: dbOLD, dbNEW and dbTrans.
DbOLD contains a restore of some old version (from a .BAK file).
DbNew contains a restore of some new version (from a .BAK file).
DbTrans contains the differences and BCPs out the data.
It appears that we can operate with no Server Roles.
We must be stipulated as DB_Owner of the above DBs.
We must run sp_ChangeDbOwner on each database.
We must have Backup/Restore privileges for each database.
And, the .BAK file must contain user definitions/privileges that allow the Restore operation.
This appears to provide a somewhat functional environment, but I'd appreciate any input relating to this topic.
Lastly, is SQL Server 2008 able to produce ANYTHING the SQL Server 2000 can digest? We still have to produce data
to support our SQL Server 2000 sites. My research hasn't indicated that that is possible.
Thanks for reading this, any responses are greatly appreciated.
Thanks,
Wayne
