This is actually quite broad subject. First of all when you create a user, it will not have any initial
unless privileges have been granted to
public role. Every user is member of public.
Also keep in mind that public role exists in every database but also as a server wide role. So all privileges granted to either the server role or the public role in the database user is accessing are effective to the user.
The next thing is the privilege you want to grant. While you can grant the privilege directly to the user, I would strongly recommend using
roles. Most likely you have a bunch of objects you want to grant access to certain group of users. In such case if you create a role and grant privileges to that role, you can easily grant privileges to users just by granting a role to them.
When you use role, you don't have to create a role for each unique privilege combination because roles are cumulative. If several rows has been granted to a user, the privileges for that user is the sum of privileges from all granted roles.
One thing that is good to know is that typically roles grant privileges but they can also
deny privileges. In certain situations it makes sense that roles are granted to people and then some of the given privileges are denied from those users. Deny always overrides grant so even if privileges are accumulated from all granted roles, deny is more powerful. But to keep things simple, I would suggest avoiding deny unless you really need it. It's often much easier to manage and understand the privileges when they are granted using small enough roles .
Few links you may find useful:
-
Server and Database Roles in SQL Server | Microsoft Docs[
^]
-
Server-Level Roles - SQL Server | Microsoft Docs[
^]
-
CREATE ROLE (Transact-SQL) - SQL Server | Microsoft Docs[
^]
-
GRANT Object Permissions (Transact-SQL) - SQL Server | Microsoft Docs[
^]
-
DENY (Transact-SQL) - SQL Server | Microsoft Docs[
^]
Submit an article or tip