Logins

[Franco]

I need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later.
I clearly need to maintain all the security options for the new logins.
Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that?
Thank you.

[Tom Goltl]

Franco,
Just trying to understand what is happening. You want to copy 80 logins, keep them somewhere else, delete them from the server, and then move the 80 that you copied back onto the server. If this is what you need, I think I can help you. Let me know.

Tom

------------
Franco at 7/10/01 8:16:31 AM


I need to copy 80 logins within the same SQL Server (7.0 SP1) from 80 "old" logins that I'll delete later.
I clearly need to maintain all the security options for the new logins.
Is there a way to do this, adding the logins with the new name and granting all security options? Is there a script that will do this task or can somebody help me in doing that?
Thank you.

[Franco]

Tom,
Thank you for your answer, but I just receive a query that I think may solve my problem.Just to let you know more in detail I have this 80 logins and I need to rename them all. The rename function is not present so I need to copy this logins and give them a new name.I also need to give this logins the same security options hold by the old logins.(sp_grantdbaccess,sp_addrolemember)
This is the query I receive:

START OF THE QUERY:

This will give you output to copy into the QA window and run against your
new DB.

Print 'Add SS logins to SS'
select
'EXEC sp_addlogin ' + name
from
sysusers
where
issqluser = 1
and
name not in ('INFORMATION_SCHEMA','guest',&#39 ;dbo&#39

Print 'Give SS users access to the Database'

select
'EXEC sp_GRANTDBACCESS ' + name

from
sysusers
where
issqluser = 1 AND

name not in ('INFORMATION_SCHEMA','guest',&#39 ;dbo&#39

CREATE TABLE #tmpUsers(UN varchar(100),GN sysname,LN varchar(100),DBN
sysname null ,UID int null ,SUSERID int null)
insert into #tmpUsers exec sp_helpuser

print 'Add NT Users to SS'

select distinct
'EXEC sp_grantlogin ''' + LN + ''''
from
#tmpUsers
WHERE
LN is not null and
LN <> &#39;sa&#39;



Print &#39;Give NT Users Access to the database&#39;

select distinct
&#39;EXEC sp_grantdbaccess &#39;&#39;&#39; + LN + &#39;&#39;&#39;,&#39;&#39;&#39; + UN + &#39;&#39;&#39;&#39;
from
#tmpUsers
WHERE
LN is not null and
LN <> &#39;sa&#39;

drop table #tmpUsers


Print &#39;Create the SQL Roles&#39;
select
&#39;EXEC sp_addrole &#39; + name
from
sysusers
where
issqlrole = 1 and
gid > 0

Print &#39;Add Role members&#39;

CREATE TABLE #Rolemembers
( DBRole sysname , MemberName varchar(100),memberSID varbinary(1000))

insert into #Rolemembers exec sp_helprolemember



select
&#39;EXEC sp_addrolember &#39;&#39;&#39; + DBRole + &#39;&#39;&#39;,&#39;&#39;&#39; + a.name + &#39;&#39;&#39;&#39;
from sysusers a join #Rolemembers b on a.sid = b.memberSID

drop table #Rolemembers

END OF QUERY

SO, Tom I hope I&#39;ll solve my problem working on this query.
Thank you again - Regards - Franco