How 2 clean up database users and roles...

[ajones]

I need to create a copy of a SQL Server 7.0 production database on a test server. The database users and roles defined on the production machine are inappropriate for the test machine (and need overhauling with respect to the production databases as well).

The DTS export wizard appears to allow me to export the production database objects without the security definitions, but I have found that DTS corrupts my stored procedures (of which I have many) in the process (and who knows what else)

I can't seem to use Enterprise Manager to purge/restructure the existing database users and roles (the Remove button is disabled). I am defined as sa for this server.

Can anyone give me (or point me to) instructions for cleaning up the existing database users and roles or reliable copying the production database without them?
Any information would be greatly appreciated.

 

[aalmeida]

ajones!

I would say that you have several ways to go, it all depends on how complex is your authentication schema, but no easy route here.

1st ( the HARD one) Option would be to script the original Db and the go throgh the loggins and roles etc and then run it to the new server than after that use DTS only to move the data.
2nd Option would be to back up the original db and then restore to the new server (there goes some of your User accounts) then you can use a combination of sp_revokedbaccess and sp_grantdbaccess to fix the lost mapping on User Accounts, after that you may visually verify permitions to make sure they are all in place.
3rd is to run a DTS and bring all objecs without object level permitions and without the user Accounts, you will have then to recreate all user accounts and assign the permitions back. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"