Re: How to share or transfer user ID and password between 2 SQL server?
From: Jobi (jobi_at_reply2.group)
Date: 05/30/03
- Previous message: Bottomless Pit: "Re: Naming Standard conventions for NT Groups"
- In reply to: Michael: "How to share or transfer user ID and password between 2 SQL server?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 30 May 2003 14:25:11 +0200
1) create a dummy-db at your source-server.
2) allow the needed users public acces to the dummy-db
3) use the "copy database wizard" from enterprise manager to transfer (copy)
your dummydb to the new server
4) in the database(s) you have restored, generate remap for users using
this script :
use Your_Restored_db -- User-db
go
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid')
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
print 'if not exists (select * from master.dbo.syslogins where loginname =
N''NtDomein**\' + @username + ''')'
print ' begin '
print ' exec sp_grantlogin N''NtDomein**\' + @username + ''''
print ' exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+
db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action =
''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action =
''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' +
@username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** opgelet : exec stmt commented !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
hope this helps
Jobi
"Michael" <vbado2003@yahoo.com.sg> wrote in message
news:u#BRqoMJDHA.4048@tk2msftngp13.phx.gbl...
> Hi, all
>
> I am using a Win2000+SQL2000 Server, with a huge database running on it.
>
> Now, I setup a new server with more hard disk space to use, by using SQL
> Server Backup/Restore function, I can transfered the database to the new
> server.
>
> Can anyone tell me how to transfer all the user_id and password to the new
> server?
>
> All my existing user account and password are created in SQL Server, not
> windows domain account. I know
> they are stored in Master database, but how can I transfer them the new
> server?
> or
> Is there any way can share the user accounts and password?
>
>
> Many thanks
>
>
> Michael
>
>
>
>
- Previous message: Bottomless Pit: "Re: Naming Standard conventions for NT Groups"
- In reply to: Michael: "How to share or transfer user ID and password between 2 SQL server?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
