I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database.
However, there is no corresponding database user mapped to any of the logins, and all of the logins seem to have full access to any of the databases on the server.
In fact, each login has a check next to all the server roles.
Is this a common practice and is it safe in terms of security?
Best Answer
You can have a login to the SQL Server instance, but no access to a given database. When your login is granted access to a DB, you become a user in that DB.
As for granting max perms via roles to all logins, this is very risky, and not common, or default. Best practice is to use the very lowest perms possible that still allows users to complete their work.