Tag: SQL server

SQL Server Transaction block

This is suggested code block for creating stored procedures in SQL Server;

BEGIN TRY
	BEGIN TRANSACTION
	--All your insert/update/delete/merge goes in here
	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber
	,ERROR_MESSAGE() AS ErrorMessage
	 ROLLBACK TRANSACTION
END CATCH

While manipulating multiple tables, the transaction would be roll back if there is any error.

Drop orphan users – internal.object.permissions

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'

Login failed. The login is from an untrusted domain

If you started seeing this message;

One of the reason might be that your Azure AD password has been expired. Try to login here;

https://myapps.microsoft.com/

User Function – Self service password reset

Click on upper right corner on your user icon and select “View Account”. Click on “Change Password” tile. Enter your old password and new password. Logout and Log back in with your new password. Microsoft Authenticator or Google Authenticator is required to authenticate.

If the computers are not joined to domain then user has to open Credential Manager on their computers to store new credentials if they are using windows authentication for any cloud services.

If user has forgotten his/her password, then admin can follow Admin Function and reset user password to a temporary password. User has to go through same steps again to reset the password.

Admin Function – To Reset the password for other User

Click on Admin;

Click on Reset password under user management. Select your user, change password. This is a temporary password. Communicate this password with user and ask them to follow self service password reset.