The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Thursday, March 28, 2024 Login
Public

Change Default Schema for all users 9/3/2008 10:46:40 AM

Recently ran into an issue where the default schema for all users in a database needed to be changed to dbo.  This was a database that was upgraded from sql server 2000 to 2005, of course a schema was created for each user.  We quickly deleted the user schema's (as they weren't used), but for some reason the default schema for each user was left as their own.  There was no quick way with the gui to change this, so I generated the following query which creates the command to alter the users as an output of column1, cut and past it into a new query window and execute.

SQL 2K5 Only.
Changes the default schema for all users.
Will generate a script in column 1, cut and paste to new query window.
Will fail for Windows groups.


SELECT
'Alter user [' + u.name + '] with default_schema = dbo' as command,
u.name AS [Name],
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/User[@Name=' + quotename(u.name,'''') + ']' AS [Urn],
u.principal_id AS [ID],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
u.create_date AS [CreateDate]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = N'CO'
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K'))
and name not in ('sys','dbo','guest','INFORMATION_SCHEMA')
ORDER BY
[Name] ASC



Blog Home