SQL Server – Create Logins, Roles, Users and Grant Permission Scripts

1. Create Users on SQL Server

There are two ways of doing this depending on the requirement of the company

Windows Authentication Method

Create user name using Windows Authentication on master database for john and using Domain name “DESKTOP-E6MM8JA\”

USE [master]
GO
CREATE LOGIN [DESKTOP-E6MM8JA\john] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

SQL Server Authentication Method

Create user name using SQL Server Authentication on master database for GJerry and using a password

USE [master]
GO
CREATE LOGIN [GJerry] WITH PASSWORD=N'UserInterface365', DEFAULT_DATABASE=[master]
GO

2. Users on Databases

Script to create user name on Database

To grant permission to users on database use the below script

USE [AdventureWorks2016]
GO
CREATE USER [GJerry] FOR LOGIN [GJerry];
GO

Script to GRANT permissions on Database objects

To grant permission to users on specific objects on database create user on SQL server, then database and then use the following script

USE [AdventureWorks2016]
GO
GRANT EXECUTE ON [dbo].[uspGetEmployeeManagers] TO [GJerry];
GRANT SELECT, UPDATE, INSERT ON [Person].[Address] TO [GJerry];

3. Roles

There may be a requirement to create a role for the application to connect to the database in that case you will have to first create the role on database and grant permissions for the roles to specific objects and also give permission to the users on that role

Script to Create Roles on Database

USE [AdventureWorks2016]
GO
CREATE ROLE Reportusers;  
GO  

Script to add users to Roles

USE [AdventureWorks2016]
GO
ALTER ROLE [Reportusers] ADD MEMBER [KLambert];

Script to Grant permissions to Roles on Database Objects

USE [AdventureWorks2016]
GO
GRANT SELECT,INSERT on [Person].[Person] TO [Reportusers]