[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

SQL 2005 Export user securables

I have a database table that has a user with 40 or 50 securable references to SPROC, tables etc.

Is there a way to export those securable references along with their associated granted permissions so they can be applied to the same user in a test environment?

i.e.   Sproc1     stored procedure
         execute
         Table1     table
          execute, insert

Thanks
0
jdr0606
Asked:
jdr0606
1 Solution
 
Brian CroweDatabase AdministratorCommented:
Options include:

Third-party tool (i.e. RedGate SQL Compare)
Script (i.e. http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror)
0
 
Deepak ChauhanSQL Server DBACommented:
First transfer the logins to testing instance by using the scripts in below Microsoft link.
 http://support.microsoft.com/kb/918992

Generate the scripts of the database by using the generate scripts wizard.
Run the database scripts on to testing instance. –method one
or
Take the backup of database and restore it on the testing environment. –method 2
Find the orphan user in database
Use <[test database]>
Execute sp_change_users_login 'report'
Go
Execute sp_change_users_login 'auto_fix', ‘<user name>’
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now