When I scan my database, it shows one of the result like VA1143 'dbo' user should not be used for normal service operation in A Vulnerability Assessment scan
They have suggested to "Create users with low privileges to access the DB and any data stored in it with the appropriate set of permissions."
I have browse regarding the same to all form but cannot get the correct suggestion yet. Could you please suggested your idea or where i have to create the user and grand the permission. Since we have only one schema structure in our DB.
About "Create users with low privileges to access the DB and any data stored in it with the appropriate set of permissions.", the first thing you should know is the Database-Level Roles.
Create users with low privileges means that the use does not have the alter database permission.
When we create the user for the database, we need to grant the roles to it to control it's permission to the database.
For example, bellow the the code which create a read-only user for SQL database:
--Create login in master DB
USE master
CREATE LOGIN reader WITH PASSWORD = '<enterStrongPasswordHere>';
--create user in user DB
USE Mydatabase
CREATE USER reader FOR LOGIN reader;
GO
--set the user reader as readonly user
EXEC sp_addrolemember 'db_datareader', 'reader';
For more details, please reference:
Hope this helps.