Granting privs in SQL Server

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]