Help with scripting to change users default database

Question

Help with scripting to change users default database

Martin Balbo

SSC Eights!

Points: 874
More actions
January 26, 2006 at 9:55 am

#110713

Hi friends!

I have to change the default database of 150 users (Now Default Db is master )
I tought how to make an script to do this, like a cursor doing a "select name from syslogins where...... " but how can i change the user's default db property?

Thanks for help.

Tincho

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Build a query like the following:

SELECT 'EXEC sp_defaultdb ''' + name + ''', ''<new database>'''
FROM syslogins

And the output should be what you want. Out of curiosity, though, why are you changing from a default db of master?

K. Brian Kelley
@kbriankelley

Martin Balbo SSC Eights! Points: 874 More actions · Answer 2

Thanks for your Reply. This is a audit issue. They say that a user need to have a default database different that master (preferably the users databases)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Okay... but whoever is conducting the audit hopefully realizes that this doesn't keep the user out of master. The guest user is enabled in master (and is required) and therefore if a user can log on to SQL Server or execute a typed in query, the user can get to master.

K. Brian Kelley
@kbriankelley