SQL Server database ownership

Another memory aid.

After importing a database into SQL Server 2008, run the following query to re-associate the privileges of the user:

BEGIN
DECLARE @username VARCHAR(255),
 @query NVARCHAR(2000)
 
 DECLARE users CURSOR
 FOR SELECT sysusers.[name]
 FROM sysusers 
 JOIN master.dbo.syslogins sl
 ON sysusers.[name] = sl.[name] COLLATE Latin1_General_CI_AS
 WHERE sysusers.issqluser = 1 AND
 (sysusers.sid IS NOT NULL AND
 sysusers.sid <> 0x0) AND
 SUSER_SNAME(sysusers.sid) IS NULL AND
 sysusers.[name] <> 'dbo'
OPEN users
 FETCH NEXT FROM users INTO @username
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @query = 'exec sp_change_users_login ' + '@Action = ''Auto_Fix'', ' + '@UserNamePattern = ''' + @username + ''''
 PRINT @query
 EXEC sp_executesql @query
 FETCH NEXT FROM users INTO @username
 END
 CLOSE users
 DEALLOCATE users
END

Apparently, in SQL Server 2012 this is no longer necessary.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s