Map SQL Logins after restore

Finally got around to sorting out orphaned users. Create two procedures... Here it is.

Put these procedures in the master database..

The second procedure loops thru each database (using the first procedure) and populates a temp table.The select statement at the end of GetOrphanedUsers_AllDatabases simply builds the string to execute.

--Here is the first proc.
CREATE Proc [dbo].[GetOrphanedUsers]
@databasename sysname
as
set nocount on

Create table #Fitz
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

declare @sql varchar(1000)
set @sql = '
use '+@databasename+'
insert into #Fitz
(DB, OrphanedUserName, UserType)
SELECT db_name() as ''DB'', a.name AS OrphanUserName, a.type_desc AS UserType
FROM sys.database_principals a
LEFT OUTER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE b.sid IS NULL
AND a.type In (''S'', ''U'', ''G'')
AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')

select * from #Fitz
'

exec (@sql)
GO

----Second Proc
CREATE PROC [dbo].[GetOrphanedUsers_AllDatabases]
AS
Create table #OUsers
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

DECLARE @databasename sysname
DECLARE DatabaseLoop CURSOR FOR
(select name from sys.databases where state=0 and database_id >4 )
OPEN DatabaseLoop
FETCH NEXT FROM DatabaseLoop into @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #OUsers
exec GetOrphanedUsers @databasename
FETCH NEXT FROM DatabaseLoop into @databasename
END
CLOSE DatabaseLoop
DEALLOCATE DatabaseLoop

Select 'exec ' + DB + '..sp_change_users_login ' + '''' + 'auto_fix' + '''' + ',' + '''' + OrphanedUserName + ''''--*
from #OUsers
where orphanedUserName <> 'dbo'
order by db
GO

Trace Flag 834

We purchased new hardware (HP 980) with 32 cores, 50 GB RAM with multiple FusionIO cards (to house database files) to run SQL 2008 R2 Enterprise. We’re leveraging SQL mirroring. This physical server is the mirror node.

Long story short, after many rebuilds from the OS up, SQL performance remained degraded. Systematically, we began to rule out what was causing the issue.

At the end of the day, we added trace flag –T834 (causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool). Lock pages in memory had already been configured.

http://support.microsoft.com/kb/920093

Well, long story short, the performance of the SQL server immediately began to improve.

Interesting fact, we have dozens of other high performance SQL instances (clustered, stand-alone, etc.), (SAN storage, other FusionIO cards) and have NOT had to set the –T834 trace flag on any additional SQL instances.

If you’ve had to set the T834 flag, please let me know.