script to copy logins and users with all permissions

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.Inna,

If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.

-- Bill

"Inna" <mednyk@.hotmail.comwrote in message
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...

Quote:

Originally Posted by

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.
>

|||On Jan 25, 7:04 pm, "AlterEgo" <altereg...@.dslextreme.comwrote:

Quote:

Originally Posted by

Inna,
>
If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.
>
-- Bill
>
"Inna" <med...@.hotmail.comwrote in message
>
news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...
>
>
>

Quote:

Originally Posted by

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.- Hide quoted text -

>
- Show quoted text -

Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?|||On Jan 30, 3:26 pm, "Inna" <med...@.hotmail.comwrote:

Quote:

Originally Posted by

On Jan 25, 7:04 pm, "AlterEgo" <altereg...@.dslextreme.comwrote:
>
>
>

Quote:

Originally Posted by

Inna,

>

Quote:

Originally Posted by

If on SQL 2K, use Enterprise Manager.
Right click the database/All Tasks/Generate SQL Script.
Click the Options tab and you can select all of the logins and object
permissions you need.

>

Quote:

Originally Posted by

-- Bill

>

Quote:

Originally Posted by

"Inna" <med...@.hotmail.comwrote in message

>

Quote:

Originally Posted by

news:1169753046.376196.16430@.v45g2000cwv.googlegro ups.com...

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Hello all,
I am looking for the script, which I believe exists already.I need to
be able to populate the script for security of one database and
apply it to another database, even if it is located on another server:
1. All logins which not exist have to be created and which exists
ignored including the NT accounts
2. Users same as the old database + the existing ones stay in database
3. Passwords for the new logins.
4. All permissions/grants on all objects for the users that exists
(usually it's the case) and ignore those that don't.
I have script which does some of it, but it's not perfect, so every
time there are some errors.
Please let me know, if you need me to email script that I have. It's
pretty long so I cannot just post it in here.
Thank you in advance.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
Hello,
I cannot use Enterprise Manager, because every time it has to be done
for different database and this script is a part of another process
and it uses database name and server name as parameter.
Is there way just to populate script for any database, from query
analyzer?

AlterEgo's point is - let Enterprise manager generate the sql for you.
It will give you a starting point and you can change the code to be
specific for your application.

Script SQL 2005 database to individual files?

I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:

- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window

FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).

Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.

Thanks.
TedMSDN/BOL indicates that this is possible here
http://msdn2.microsoft.com/en-us/library/ms191299.aspx. However, it
doesn't give any clue on how to accomplish it.

Quote from that page: "The schema for generated objects can be saved in
a single SQL Script file, or in several files with each file containing
the schema of just one object."|||Ted O'Connor (toconnor@.gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/produ...px?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/produ...px?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

scrip help

I wanna to have a script that will read a sertain table in a database. If a csrtain record that is being added exists in the table already I want it not to be added into teh specific tables. If it does not exist, i want it to be added.

Here is the logic that I wanna have:

READ OPCSHTO
GET CUST_CODE + LOC_CODE
IF EXIST CUST_CODE + LOC_CODE RECORD IN DPTORGANIZATIONSLOCATIONS
GO TO READ OPCSHTO

ELSE
DO ADD TO DPT.ORGANIZATIONLOCATIONS TABLE
DO ADD TO DPTORGANIZATIONSyou can have a stored procedure with CUST_CODE & LOC_CODE as input parameters. It will first check the table for this record by doing a count(*). If this count is zero it will insert the new record.|||Originally posted by rohitkumar
you can have a stored procedure with CUST_CODE & LOC_CODE as input parameters. It will first check the table for this record by doing a count(*). If this count is zero it will insert the new record.

That sounds cool. Can you be a little more specific cause I really don't know how I would do a sp.

thanks.|||I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

===================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

OPEN cur_OPCSHTO

FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END


FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO
======================================|||Originally posted by rohitkumar
I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

===================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

OPEN cur_OPCSHTO

FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END


FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO
======================================

Thanks a bunch...I will see how it turns out. How long have you been doing this for? Thanks for understanding......pretty new at this.|||Originally posted by rohitkumar
I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

===================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

OPEN cur_OPCSHTO

FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END


FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO
======================================

You think you would be able to right a descripting by each command, thatway I could understand what is going on and I can understand it better?
thanks for your help.|||I've tried writing a short description, let me know if something is missing

=========================================
CREATE PROCEDURE USP_insert_dtporgloc AS
BEGIN
/* variable declaration */
DECLARE @.CUST_CODE NCHAR(20)
DECLARE @.LOC_CODE NCHAR(20)

/* this will fetch CUST_CODE , LOC_CODE from cur_OPCSHTO and store it in an cursor "cur_OPCSHTO" (sort of an array) */
DECLARE cur_OPCSHTO SCROLL CURSOR FOR
SELECT
CUST_CODE , LOC_CODE
FROM
OPCSHTO

/* open this cursor for fetching the data */
OPEN cur_OPCSHTO

/* fetch first of the stored values and put them in these variables */
FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE

/* repeat the process till last record in the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN

/* count the number of recs in DPTORGANIZATIONSLOCATIONS having CUST_CODE , LOC_CODE. If this count is zero then insert this as a new record in the DPTORGANIZATIONSLOCATIONS table*/
IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @.CUST_CODE and LOC_CODE = @.LOC_CODE) = 0
BEGIN
insert into DPTORGANIZATIONSLOCATIONS values (@.CUST_CODE, @.LOC_CODE, ...etc etc...)
END

/* fetch next of the stored values in the cursor and put them into variables*/
FETCH NEXT FROM cur_OPCSHTO
INTO @.CUST_CODE, @.LOC_CODE
END /* END corresponding to WHILE, the process between WHILE and END will repeat till there are no more records in the cursor*/

/* close the cursor and deallocate the resources*/
CLOSE cur_OPCSHTO
DEALLOCATE cur_OPCSHTO
Return
END
GO