Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL 2005 Export user securables

Posted on 2014-01-27
2
Medium Priority
?
881 Views
Last Modified: 2014-02-15
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
Comment
Question by:jdr0606
2 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39813525
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
 
LVL 16

Accepted Solution

by:
Deepak Chauhan earned 1500 total points
ID: 39862236
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question