Security – Page 3 – My Point of View

View effective permission of single user

execute as user = 'SomeUserName' -- Set this to the user name you wish to check
select * from fn_my_permissions(null, 'DATABASE') -- Leave these arguments, don't change to MyDatabaseName
order by subentity_name, permission_name
revert

This will list all effective permission for this user;

Read, Write and Execute permission to SQL Login

I have a web application. This application connect to MS SQL SERVER 2017 for data manipulation (SELECT, UPDATE, DELETE, INSERT) and execute stored procedures. The application also run SQL Agent jobs. I need to create a user in the database to allows application to connect and execute queries, stored procedures and run SQL Agent jobs.

Open SSMS (sql server management studio) login through sysaddmin acount e.g. sa

Make sure “user1” has connect permission to yourDB.

Execute this query

use yourDB
go

GRANT EXECUTE TO user1
GRANT SELECT TO user1
GRANT INSERT TO user1
GRANT UPDATE TO user1
GRANT DELETE TO user1

and also execute this

GRANT ALTER ON SCHEMA::dbo TO user1

where user1 is your user

If we want to allow this user to run sql agent jobs, we need to add it to “SQLAgentOperatorRole”. This role will allow the user to run any job on the server.

Now to SQL Agent permissions;

USE [msdb]
GO
CREATE USER [user1] FOR LOGIN [user1]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [user1]
GO

Make sure user has these permissions in MSDB database;

This is a good article on setting up jobs and an idea to integrate those jobs in UI.

How do I let someone execute a SQL Agent job they don’t own?

for troubleshooting, assign user to “sysadmin” Server Role. Make sure to revoke this permission afterwards.

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;

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, RBAC, Storage 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:

Privilege escalation to Azure PIM and the Global Admin Account;
Ransomware Attack;
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.

The three most effective and dangerous cyberattacks to Azure and countermeasures (part 1 – attack all the public and private IP addresses in Azure)

Another good one from linkedin;

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