Creating a Loop to Iterate Through a SQL Table and Change Default Database

Question

Post reply

Creating a Loop to Iterate Through a SQL Table and Change Default Database

Simbo_mk

SSC-Addicted

Points: 450
More actions
September 26, 2013 at 3:36 pm

#298298

Hello!
I was wondering whether someone could help me with creating a loop to iterate through a table (sys.syslogins) and
change the default databases to a different database for all sql logins. If successful, the users should be directed to the newly
selected database at logon. We have nearly 150 users that login to the server.
So far, I have a script that gives me a list of all logins and databases, as shown below:
select name,
loginname,
dbname as DefaultDB,
DATABASEPROPERTYEX(dbname, 'Status') as DBStatus
from sys.syslogins
order by DBstatus
I thank you very much in advance!
MK

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You should look up ALTER LOGIN in BOL.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

use msdb

select 'ALTER LOGIN [' + name + '] WITH DEFAULT_DATABASE = YOUR_DB; ' from sys.syslogins where language = 'us_english'

and name not like 'NT %' and name <> 'sa' and name not like '##%'

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Bhuvnesh (9/30/2013)
use msdb
select 'ALTER LOGIN [' + name + '] WITH DEFAULT_DATABASE = YOUR_DB; ' from sys.syslogins where language = 'us_english'
and name not like 'NT %' and name <> 'sa' and name not like '##%'

Starting with SQL Server 2005 you should really use sys.server_principals for this instead of sys.syslogins which is a compatibility view for SQL Server 2000 compatibility.