Granting privs in SQL Server is done in three steps. As first step, one must create a login to the SQL Server. This can be seen as getting the entrance to the data fort.
CREATE LOGIN lezer WITH PASSWORD = 'password' GO
One must then get entrance to the database. One must create a user who has access to the system files that steer the database.
use master CREATE USER lezer FOR LOGIN lezer WITH DEFAULT_SCHEMA =dbo
Subsequently, a user is created on the database itsself.
use [sqlservertomvanmaanen]
CREATE USER lezer
FOR LOGIN lezer
WITH DEFAULT_SCHEMA =dbo
Then a role is created that provides a pattern of privs that is given to a user. A user is then added as a member to that role.
CREATE ROLE [db_Lezer]
ALTER ROLE [db_Lezer] ADD MEMBER lezer
Finally, privs are granted to the role.
GRANT CREATE FUNCTION TO [db_Lezer]
GRANT CREATE PROCEDURE TO [db_Lezer]
GRANT CREATE SCHEMA TO [db_Lezer]
GRANT CREATE SYNONYM TO [db_Lezer]
GRANT CREATE TABLE TO [db_Lezer]
GRANT CREATE VIEW TO [db_Lezer]
GRANT DELETE TO [db_Lezer]
GRANT EXECUTE TO [db_Lezer]
GRANT INSERT TO [db_Lezer]
GRANT REFERENCES TO [db_Lezer]
GRANT SELECT TO [db_Lezer]
GRANT SHOWPLAN TO [db_Lezer]
GRANT UPDATE TO [db_Lezer]