Tag: SQL server

I did a database restore from development to staging. Couldn’t connect to database using user login, Adam. Selected General tab on properties by clicking on Database->Security->User->Adam. The User type is “SQL user without login”. It seems restore process didn’t connect to the logins.

I try to drop the user;

DROP USER [qsadb]
GO

I keep getting this error;

• Msg 208, Level 16, State 1, Procedure ddl_cleanup_object_permissions, Line 8 [Batch Start Line 2]
• Invalid object name ‘internal.object_permissions’.

So, this is an orphan user and issue is with permissions. This needs to be fixed. But how?

Here are the steps that I have used;

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-language) that has to match as well.

This used to be a pain to fix, but currently (SQL Server 2000, SP3 and up) there is a stored procedure that does the heavy lifting.

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'AUTO_FIX', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'AUTO_FIX', 'user', 'login', 'password'

Is it a good idea to use table locks? Here is a list of articles that talk about this;

Best practice to use locking hints

Run this script;

RESTORE DATABASE [mydatabase] WITH RECOVERY

Resources;

https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/

The easies way to do this using SQL Server 2017 and up;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	--check there is a '.' in ExcelFileName
	CASE WHEN @FileName LIKE '%.%' THEN
		REVERSE(left(REVERSE(@FileName), CHARINDEX('.', REVERSE(@FileName)) - 1))
	ELSE ''
	END ExcelFileExtension
WHERE 1=1

Earlier versions can use this approach;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	CASE 
         WHEN @FileName LIKE '%.%' THEN 
			RIGHT(@FileName, LEN(@FileName) - CHARINDEX('.', @FileName)) 
         ELSE '' 
       END ExcelFileExtension 
WHERE 1=1