October 5, 2015 at 11:25 am
I have restored some 2008 databases to a 2012 Dev server. As part of this, I'm running a fix users cursor against each database to resynch logins with the restored dbs. But I'm running into some weird errors.
I'm deliberately not restoring all databases from production into this environment. Nor am I restoring all users. So I expect a few orphans. What I don't expect is my code to error out with the following error:
Msg 911, Level 16, State 4, Procedure XX_XXX_DatabaseAuditLog_DDL, Line 21
Database 'XXX_AuditLog' does not exist. Make sure that the name is entered correctly.
This does not happen in SQL 2008. Any thoughts on what could be going on? Fix users code below.
/*-----------------------------------------------------------------------------
After restoring a database,fix users that have a corresponding login on this server
-----------------------------------------------------------------------------*/
-------------------------------------------------------------------------------
-- Declarations
-------------------------------------------------------------------------------
DECLARE
@User_Namevarchar(255),-- User Name
@Adhoc_SQLnvarchar(2000)-- Used with sp_ExecuteSQL
-------------------------------------------------------------------------------
-- Declare Cursor
--Only Users that need fixing that also have a valid login on this server
-------------------------------------------------------------------------------
DECLARE User_Cursor CURSOR FOR
SELECT
DP.[name]
FROM
sys.database_principals DP
INNER JOIN
sys.server_principals SP
ON
DP.[name] = SP.[name]
WHERE
DP.type_desc = 'SQL_USER'
AND
(DP.sid IS NOT NULL ANDDP.sid <> 0x0)
AND
SUSER_SNAME(DP.sid) IS NULL
-------------------------------------------------------------------------------
-- Open Cursor of Users
-------------------------------------------------------------------------------
OPEN User_Cursor
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
-------------------------------------------------------------------------------
-- Process Each User and issue Fix command via Sp_Change_Users_Login
-------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build Command String
SET @Adhoc_SQL = 'EXEC sp_change_users_login '+
'@Action = ''Auto_Fix'', ' +
'@UserNamePattern = ''' + @User_Name + ''''
-- Display Command issued
print @Adhoc_SQL
-- Execute Command
EXEC sp_ExecuteSQL @Adhoc_SQL
-- Get the next Row
FETCH NEXT FROM
User_Cursor
INTO
@User_Name
END
-------------------------------------------------------------------------------
-- Cursor Cleanup
-------------------------------------------------------------------------------
CLOSE User_Cursor
DEALLOCATE User_Cursor
GO
October 5, 2015 at 11:31 am
Nevermind. I think I found it. Someone hid a database trigger in our DB.
Why it doesn't cause the same issue on other non-prod SQL 2008 environments, I have no idea.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply