Restore Prod DB Into Dev DB Apply Dev Security

I have a SQL Server 2005 production database that has approximately 50 users
and several application roles. What is the best way to restore production
database into your development database and users in the updated development
database with the same security permissions before the restore was applied?

I would like to make sure the development users have the same permissions
before and after the database restore has been applied.

Please help with the steps and procedures to complete this task.


Thank You,

Joe K. (Joe K. DeleteThis @discussions.microsoft.com) writes:
> I have a SQL Server 2005 production database that has approximately 50
> users and several application roles. What is the best way to restore
> production database into your development database and users in the
> updated development database with the same security permissions before
> the restore was applied?
>
> I would like to make sure the development users have the same permissions
> before and after the database restore has been applied.
>
> Please help with the steps and procedures to complete this task.

If I understand your question, you have a database A. You are now throwing
that database away and in place of that you put a copy of the database B.
However, you want to migrage the security scheme of what you had in A
to the copy of B.

I can't think of any convenient and quick way to script all permissions.
You could script the entire database, the one you are about to replace,
by right-clicking the database and selecting Tasks->Generate Scripts.
Opt to script all objects, and make sure that you have "Script object-level
permissions". You would then have to extract the GRANT statements from
the file. You should definitely take some samples, to make sure that it
includes all sorts of permission you know that you have used.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx