How to Drop Orphan User in SQL Server (Msg 15138)

I am not able to drop a SQL user and keep getting this error message;

Msg 15138, Level 16, State 1, Line 5

The database principal owns a schema in the database, and cannot be dropped.

There is an orphan user who owns a schema or role and can not be dropped until user is detached from schema/role.

First see if there is any role associated and remove it;

-- Query to get the orphan users
EXEC sys.sp_change_users_login 'REPORT'

-- Query to get the user associated Database Role
SELECT 
	DBPrincipal_2.name as [Role], DBPrincipal_1.name as [OWNER] 
FROM sys.database_principals as DBPrincipal_1 
INNER JOIN sys.database_principals as DBPrincipal_2 
	ON DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id 
WHERE DBPrincipal_1.name = 'ADDUSER'

--Query to fix the role
ALTER AUTHORIZATION ON ROLE::[db_owner] TO [dbo]

SSMS STEPS: Object Explorer->Target Server->Target Database->Security->roles->Right click on database role. Change user name to your selected name or “dbo” and click OK.

Now fix the issue where we will transfer the ownership of the database role/schema to dbo.

----*** Query to get the user associated schema
select * from information_schema.schemata
where schema_owner = MyUser'

--Query to fix the error Msg 15138 on database schema
ALTER AUTHORIZATION ON SCHEMA::[MyDatabaseSchema] TO [dbo]

--Query to drop the user
DROP USER [MyUser]
GO

SSMS STEPS: Object Explorer->Target Server->Target Database -> Security->Schemas->Right Click on schema->Change user name to your selected name or “dbo”.

Schema and/or database role has been transferred to “dbo”. You are safe to drop the user.

How to get Azure QR Code?

If you want to re-register for Azure multi-factor authentication and use Authentication app on a new/existing phone, follow these steps;

Login to Azure Portal -> Active Directory -> Your user account –> Profile

Click on Authentication methods link.

Click on Access Panel Profile link. This will take you to your profile page.

Click on Additional security verification link.

Select this option to view QR coce;

You are ready to restore your credentials.

Resources;

https://docs.microsoft.com/en-us/azure/active-directory/user-help/multi-factor-authentication-setup-auth-app

How to secure Azure VM

Trying to gather resources that can help to secure Azure environment;

There are three most essential areas in Microsoft Azure, RBACStorage and Networking, everything in Azure depends on these three main pillars, and considering these areas, I identified the 3 topmost dangerous cyberattacks, below the TOP Parade:

  1. Privilege escalation to Azure PIM and the Global Admin Account;
  2. Ransomware Attack;
  3. Attack to the public and private IP addresses;

All these attacks are extremely dangerous and effective. However, the privilege escalation is the most dangerous because it can escalate a top-level, which means no more control in the entire cloud and company.

Another good one from linkedin;

https://www.linkedin.com/pulse/explain-example-vpn-gateway-expressroute-michelle-xie/

Prevent spam on your entry level Blog

The settings for controlling comments in WordPress is available under Settings > Discussion. Please note that this is the manual method of preventing/combating spam and is usually the most effective when you have a few comments every day. However, if you have over 1000 daily comments, it is more feasible to use an antispam plugin.

Let’s discuss certain ways to use these default WordPress settings to prevent spam. We will discuss one section at a time.

Default Article Settings

Simply disable trackbacks and pingbacks to save yourself from half the spam traffic. Only allow people to post comments on new articles.

Other Comment Settings

It is almost always necessary for the comment author to enter his name/email before commenting. WordPress enables this by default. If you require users to log in before leaving comments then this will cut down on a significant amount of spam This step might be a turn off for few legit readers who want to leave a comment but don’t want to sign in. Therefore you must carefully analyze before enabling this feature.

You should disable comments on posts older than 90 days in a fairly active blog. However, if you keep updating articles, make sure to change the published date so that the 90 days boundary doesn’t overlap. Keep Threaded comments to the default or increase it if required.

E-mail Me Whenever

You can use this feature if you don’t get thousands of comments on your posts. You will get email notifications for every comment that pops up and you can mark it spam right away cutting down on a section of spam comments.

Before a Comment Appears

Allowing readers who have previously left a comment, to leave a comment again without requiring any approval, will help you cut down the legit comments in the moderation queue. You’ll only have to focus on the remainder of users, mostly of which will be spam.

Comment Moderation

I suggest you use a value of 2. This ideally allows guest bloggers to leave at most one outgoing link (link bait) in their comment. Building an effective comment moderation blacklist is a very time-consuming process, with equally beneficial payoffs. However, you can leverage this setting as an effective profanity filter. Simply add the profane words to the list and all such comments will be added to the moderation queue.

Comment Blacklist

Comment Blacklist is stricter version of the Comment Moderation Blacklist, where if a comment contains a blacklisted word, it is sent to the spam queue, instead of the moderation queue. The benefit – saves your time.

Adding a separate email account as an owner subscription

My friend has created the Azure subscription using this email address, foo.inc@outlook.com. Azure has created a domain fooincoutlook.onmicrosoft.com in Azure Active Directory.

Me and my friend share same subscription with same foo.inc@outlook.com email address to provision services Azure. There are occasional disruptions in my sign-in and I see a login pop up window. It asks me to type-in our shared email address to get a code and authenticate in Azure. I contact my friend and solve login issue. This is a waste of time.

To solve this issue, navigate to Active Directory -> Manage -> User and create a new user;

adam@fooincoutlook.onmicrosoft.com

Navigate to Azure subscription -> Access control (IAM) -> Add -> Add role assignment;

By using adam@fooincoutlook.onmicrosoft.com, We can share a single subscription but can use our own email accounts to provision resources.

There are other ways to manage identities but I have found this an easier and quicker fix.