Needs to change default database for all logins. help me

• p1naga

  SSC Veteran

  Points: 296

  More actions

  December 13, 2007 at 3:41 pm

  #185001

  hi

  I have some 1400 logins. I have to change default database for all this logins. Can i update syslogins table? will there be any problem if i do that? I have to point all 1400 logins to the same database.

  Thanks in advance

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  December 14, 2007 at 1:42 am

  #760101

  This should do the job:

  DECLARE @login nvarchar(128), @isql nvarchar(2000)

  DECLARE c1 CURSOR FOR

  SELECT name from syslogins where name <>'sa'

  OPEN c1

  FETCH NEXT FROM c1

  INTO @login

  WHILE @@FETCH_STATUS <> -1

  BEGIN

  SET @isql = 'EXEC sp_defaultdb ' + QUOTENAME(@login, '''') + ' , ''YourDatabase'''

  Exec sp_executesql @isql

  FETCH NEXT FROM c1

  INTO @login

  END

  CLOSE c1

  DEALLOCATE c1

  Just change the name to your database name and maybe you need to exclude some more logins besides "sa".

  [font="Verdana"]Markus Bohse[/font]

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  December 20, 2007 at 2:55 pm

  #761924

  How about update master..sysxlogins?

  UPDATE master..sysxlogins SET dbid = ? WHERE ...