Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?
Hi
"Kristen" wrote:

> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John
|||No....I will look into that. Thanks!
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John
|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John
|||Hi
"Kristen" wrote:

> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John
|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:
> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:
> Hi
> "Kristen" wrote:
> > For DR purposes, I need to have a job that automatically scripts out the
> > creation of the logins and users for a database.
> > Looked at using sp_helprevlogin, but that is for different versions of SQL.
> > Looked at SCPTXFR to script out MASTER, but it is not including the logins,
> > only users.
> > Anyone have any ideas?
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:
> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure you verify this. Based on
for what purpose you want this script, it might be very important for the logins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DMO does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>> Can you think of another way that does not entail alot of programming?
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQL
> Server version!
> Why not just backup the system databases?
> John
>

Automatically Transfer logins/users to a script file

For DR purposes, I need to have a job that automatically scripts out the
creation of the logins and users for a database.
Looked at using sp_helprevlogin, but that is for different versions of SQL.
Looked at SCPTXFR to script out MASTER, but it is not including the logins,
only users.
Anyone have any ideas?Hi
"Kristen" wrote:

> For DR purposes, I need to have a job that automatically scripts out the
> creation of the logins and users for a database.
> Looked at using sp_helprevlogin, but that is for different versions of SQL
.
> Looked at SCPTXFR to script out MASTER, but it is not including the logins
,
> only users.
> Anyone have any ideas?
Have you looked at DMO and the logins collection?
John|||No....I will look into that. Thanks!
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John|||Can you think of another way that does not entail alot of programming?
"John Bell" wrote:

> Hi
> "Kristen" wrote:
>
> Have you looked at DMO and the logins collection?
> John|||Hi
"Kristen" wrote:

> Can you think of another way that does not entail alot of programming?
>
I would expect the DMO to take less then 12 lines of code!
I don't reallty see why you have an issue with sp_help_rev_login, it will
reside in the master database and have the same interface regardless of SQL
Server version!
Why not just backup the system databases?
John|||> I would expect the DMO to take less then 12 lines of code!
I'm not certain how well DMO handles SID number and password, so make sure y
ou verify this. Based on
for what purpose you want this script, it might be very important for the lo
gins to have the same
SID and pwd as in the originating SQL Server, so make sure you check that DM
O does it the right way.
This is, btw, the beauty of using sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B6C82C3-6A1E-4EE0-B6FF-B5385176DA9F@.microsoft.com...
> Hi
> "Kristen" wrote:
>
> I would expect the DMO to take less then 12 lines of code!
> I don't reallty see why you have an issue with sp_help_rev_login, it will
> reside in the master database and have the same interface regardless of SQ
L
> Server version!
> Why not just backup the system databases?
> John
>|||If you contact me, I will share a script. It will script logins,
users, system and database role membership, and indiviudal grants with
passwords preserved in SQL Server 2005.
Terry

Automated testing | How to "force" GETDATE() function to return specific value?

Hello,

Our QA team have running a lot of test scripts (for automated regression
testing), they run them on the different databases (Oracle/MS SQL).
Several of those tests are dependent on the current date/time. In order to
be
able to use them efficiently, we changed the current date/time on the QA
database server to a specific date/time before starting the scripts, so we
are sure the test scripts always run in the same environment.

Resetting the date/time of the database server gives us more and more
problems (OS problems, backup/ virusscan, ...).

It is possible to fix the problem with SYSDATE function on Oracle by setting
FIXED_DATE init parameter.

Is it possible to 'change' the current date/time on 'database' level,
instead of on OS level for MSSQL2000?
Do you know other means to do such things?

Thanks in advance,

Konstantin"Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> Hello,
> Our QA team have running a lot of test scripts (for automated regression
> testing), they run them on the different databases (Oracle/MS SQL).
> Several of those tests are dependent on the current date/time. In order to
> be
> able to use them efficiently, we changed the current date/time on the QA
> database server to a specific date/time before starting the scripts, so we
> are sure the test scripts always run in the same environment.
> Resetting the date/time of the database server gives us more and more
> problems (OS problems, backup/ virusscan, ...).
> It is possible to fix the problem with SYSDATE function on Oracle by
setting
> FIXED_DATE init parameter.
>
> Is it possible to 'change' the current date/time on 'database' level,
> instead of on OS level for MSSQL2000?
> Do you know other means to do such things?
> Thanks in advance,
> Konstantin

I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
return the same value every time it's called? If so, I don't believe there's
any way to do this in SQL Server. You could create your own function called
dbo.getdate(), and use that, but then it wouldn't be a valid test of your
'real' code.

In any case, it's not clear why you would want to always test with the same
datetime value - you wouldn't prove anything except that your code works
with one particular datetime, and that probably isn't desirable. It might be
better to put some work into establishing what the test results should be
for a given datetime input value, and validate your code that way.

If I've misunderstood, or if this doesn't help, perhaps you can clarify
exactly what you want getdate() to return, and how you want to use it.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fc25218$1_3@.news.bluewin.ch...
> "Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
> news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> > Hello,
> > Our QA team have running a lot of test scripts (for automated regression
> > testing), they run them on the different databases (Oracle/MS SQL).
> > Several of those tests are dependent on the current date/time. In order
to
> > be
> > able to use them efficiently, we changed the current date/time on the QA
> > database server to a specific date/time before starting the scripts, so
we
> > are sure the test scripts always run in the same environment.
> > Resetting the date/time of the database server gives us more and more
> > problems (OS problems, backup/ virusscan, ...).
> > It is possible to fix the problem with SYSDATE function on Oracle by
> setting
> > FIXED_DATE init parameter.
> > Is it possible to 'change' the current date/time on 'database' level,
> > instead of on OS level for MSSQL2000?
> > Do you know other means to do such things?
> > Thanks in advance,
> > Konstantin
> I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
> return the same value every time it's called? If so, I don't believe
there's
> any way to do this in SQL Server. You could create your own function
called
> dbo.getdate(), and use that, but then it wouldn't be a valid test of your
> 'real' code.
> In any case, it's not clear why you would want to always test with the
same
> datetime value - you wouldn't prove anything except that your code works
> with one particular datetime, and that probably isn't desirable. It might
be
> better to put some work into establishing what the test results should be
> for a given datetime input value, and validate your code that way.
> If I've misunderstood, or if this doesn't help, perhaps you can clarify
> exactly what you want getdate() to return, and how you want to use it.
> Simon

Thank you for replay.

Yes. You are right. The FIXED_DATE lets you set a constant date that SYSDATE
will always return instead of the current date.

The main problem is that if you take a fixed test database, and run for
example a report, the output of that report can be (very) different if you
run this report today vs. the run you will do tomorrow vs. ... Other typical
example: our application (financial one) reacts differently if you want to
create some entities with dates in the past (different execution flow).

We are using Rational Robot in the automated testing. It validates that all
system reaction and outcome is identical to the reference run (verification
points). If the 'current date/time' is changed since the 'reference' run,
you get a lot of (unnecessary) errors/warnings and failed verification
points. That's why we need to run test scripts with specific datetime value.

Of course, automated tests are not intended to replace all other tests,
their purpose to supplement another tests. Automated test scripts are mainly
used for regression testing. Since it is very difficult and time-consuming
to make Robot test scripts time independent it is acceptable for us to
execute tests with fixed datetime.

Unfortunately, calling of new dbo.getdate() function will cause modification
of application. It is time-consuming and task. That's why our main goal is
to solve the problem by means of Oracle/MSSQL parameters. Moreover, your
solution with dbo.getdate() will not work in a case when we need correct
datetime (not fixed).

CREATE FUNCTION dbo.getdate()
RETURNS datetime AS
BEGIN
-- return cast ('2003-01-01' as datetime) -- It is OK.
return getdate() -- It is NOT OK.
END

The GETDATE() is a nondeterministic function and it is not allowed to use it
in user-defined functions.

If we will not solve the problem by configuring MSSQL database (I think it
is most likely) our solution will be to create the following view:

CREATE VIEW v_nondeterministic AS SELECT getdate() AS getdate;

And to use SELECT getdate FROM v_nondeterministic where required.

Best regards,

Konstantin

Automated script generation

I often create scripts from SQL Server 2000 Enterprise Mgr the same way: I select all tables, check Indexes, check Constraints, check Windows text, then I go. Is it possible to automate this task further, for example running the scripting from a Stored Proc?RE: I often create scripts from SQL Server 2000 Enterprise Mgr the same way: I select all tables, check Indexes, check Constraints, check Windows text, then I go. Is it possible to automate this task further, for example running the scripting from a Stored Proc?

Q1 Is it possible to automate this task further, for example running the scripting from a Stored Proc?

A2 Yes, though for some tasks (particularly with previous versions) you may have to use cursors.|||I could, fairly quickly, write a SP that reads some system tables and do the scripting I need itself.

But, is there some way to make Enterprise Mgr to do it, without really having to write a program?|||Originally posted by Coolberg
I could, fairly quickly, write a SP that reads some system tables and do the scripting I need itself.

Well, not quickly I realize, because I need to put the ALTER TABLE ... DROP CONSTRAINT statements in the proper order.
Or could I do a ALTER TABLE ... NOCHECK ALL on all tables...?

automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!

automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!

Automated Dump/Load

Hi
Does anyone have any scripts/jobs that automate the load from prod to test
environments. Prod already has dump jobs running. What I am looking for is a
sp or job which will ready the backup history table on prod and get the
latest dump files, xcopy them to test server and load test databases having
first killed users related to the db's being loaded.
Thanks
Hi
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:D7C7807A-2833-4880-A01E-B285D2A2584F@.microsoft.com...
> Hi
> Does anyone have any scripts/jobs that automate the load from prod to test
> environments. Prod already has dump jobs running. What I am looking for is
> a
> sp or job which will ready the backup history table on prod and get the
> latest dump files, xcopy them to test server and load test databases
> having
> first killed users related to the db's being loaded.
> Thanks

Automated Dump/Load

Hi
Does anyone have any scripts/jobs that automate the load from prod to test
environments. Prod already has dump jobs running. What I am looking for is a
sp or job which will ready the backup history table on prod and get the
latest dump files, xcopy them to test server and load test databases having
first killed users related to the db's being loaded.
ThanksHi
http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:D7C7807A-2833-4880-A01E-B285D2A2584F@.microsoft.com...
> Hi
> Does anyone have any scripts/jobs that automate the load from prod to test
> environments. Prod already has dump jobs running. What I am looking for is
> a
> sp or job which will ready the backup history table on prod and get the
> latest dump files, xcopy them to test server and load test databases
> having
> first killed users related to the db's being loaded.
> Thanks