Take at look at the scripting available via SQL-DMO.
Should be able to generate the required T-SQL script in a VBScript and
the use the FileSystemObject to save it to a file.
After the restore, just reverse the process.
Thanks
Phillip Carter
Ph: +61 3 9235 1691
-----Original Message-----
From: Dylan Jones
[mailto:mssqldba-ezmlmshield-x8388120.[Email address protected]
Sent: Wednesday, 29 June 2005 1:00 AM
To: LazyDBA Discussion
Subject: RE: Scripting Database Users
All,
Sorry I need to be clear.
I have a reporting server - a sandbox really. Each day new users might
be added or dropped from this reporting server and their access rights
might also be modified. I have a job already built that completes a
backup of our production databases and then restores these database on
the reporting server. The issue is that when this takes place the
security info from the production databases is what comes over.
So... I need to script the security information on the reporting server
- save it off to file - complete the restore - and then re-establish the
security information (SQL Logins, Database Users, Object Level
Permissions) of the reporting server - back onto the reporting server.
Thanks, Dylan
-----Original Message-----
From: Paul Schlieper
[mailto:mssqldba-ezmlmshield-x63108659.[Email address protected]
Sent: Tuesday, June 28, 2005 7:48 AM
To: LazyDBA Discussion
Subject: RE: Scripting Database Users
See this for scripting logins and passwords.
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
-----Original Message-----
From: Wemhoff Dan (Contractor)
[mailto:mssqldba-ezmlmshield-x33466778.[Email address protected]
Sent: Tuesday, June 28, 2005 10:45 AM
To: LazyDBA Discussion
Subject: RE: Scripting Database Users
In em right click the DB go to all tasks. The forth option is generate
scripts. When the wizard appears click the options tab and select which
options you want. Go back to the general page and preview it the copy
and paste to a txt file or into QA.
-----Original Message-----
From: Dylan Jones
[mailto:mssqldba-ezmlmshield-x66582522.[Email address protected]
Sent: Tuesday, June 28, 2005 10:40 AM
To: LazyDBA Discussion
Subject: Scripting Database Users
Hi All,
What are the TSQL statements I can run that will
1) script all database users and roles - save to file
2) script all object level permissions - save to file
3) Script all SQL Logins - save to file
save the results to file
then
recreate
1) all database users and roles
2) all object level permissions
3) all SQL Logins
by referencing the the files created above?
Dylan Jones
Senior Business Analyst
SurfControl
Direct: 831-440-2568
Cell: 831-566-3089
Fax: 831-440-2615
Dylan.[Email address protected]
www.surfcontrol.com
Get the latest news on SurfControl and our products, subscribe to our
monthly e-newsletter, SurfAdvisory at:
http://www.surfcontrol.com/resources/surfadvisory/surfadvisory_signup.as
px
*********************************************************************
The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
email by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, distribution or any action taken or
omitted to be taken in reliance on it, is prohibited and may be
unlawful. If you believe that you have received this email in error,
please contact the sender.
*********************************************************************
SurfControl Inc.
5550 Scotts Valley Drive
Scotts Valley, California 95066
831-440-2500
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
Get the latest news on SurfControl and our products, subscribe to our
monthly e-newsletter, SurfAdvisory at:
http://www.surfcontrol.com/resources/surfadvisory/surfadvisory_signup.as
px
*********************************************************************
The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
email by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, distribution or any action taken or
omitted to be taken in reliance on it, is prohibited and may be
unlawful. If you believe that you have received this email in error,
please contact the sender.
*********************************************************************
SurfControl Inc.
5550 Scotts Valley Drive
Scotts Valley, California 95066
831-440-2500
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
IMPORTANT DISCLAIMER - THIS MAY AFFECT YOUR LEGAL RIGHTS:
Because this document has been prepared without consideration of any
specific clients investment objectives, financial situation or needs,
a Bell Potter Securities Limited investment adviser should be
consulted before any investment decision is made.
While this document is based on the information from sources which
are considered reliable, Bell Potter Securities Limited, its directors,
employees and consultants do not represent, warrant or guarantee,
expressly or impliedly, that the information contained in this document
is complete or accurate.
Nor does Bell Potter Securities Limited accept any responsibility to
inform you of any matter that subsequently comes to its notice, which
may affect any of the information contained in this document.
This document is a private communication to clients and is not intended
for public circulation or for the use of any third party, without the
prior approval of Bell Potter Securities.
Disclosure of Interest: Bell Potter Securities Limited receives commission
from dealing in securities and its authorised representatives, or introducers
of business, may directly share in this commission. Bell Potter Securities
and its associates may hold shares in the companies recommended.
Bell Potter Securities Limited ABN 25 006 390 772 AFS Licence No. 243480
MS Sql Server LazyDBA home page