default database in transfer login script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

default database in transfer login script

Author

Topic

vanbasten
Starting Member

7 Posts

Posted - 2011-04-04 : 16:20:23

I need to make a copy of db from sql server 2000 to sql server 2000. I am following this instruction (Method 1) to create sp_help_revlogin procedure to transfer logins.

http://support.microsoft.com/kb/246133

However, the script generated does not have the command to set default database for logins. Am I missing something or am I supposed to set the default database manually for each logins even I am using the script?

Thanks.

llinares
Starting Member

3 Posts

Posted - 2011-04-05 : 10:22:30

You need to add code to make it do that.
DECLARE @defaultdb sysname

BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
-- ADD THIS LINE:
SELECT @defaultdb = dbname from master..syslogins where [name] = @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group

Then when you set @tmpstr modify it to something like this:

SET @tmpstr = @tmpstr + @CR + 'EXEC master..sp_addlogin ''' + @name + ''', NULL, @defdb = ' + @defaultdb + ', @sid = ' + @SID_string + ', @encryptopt = '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources