Managing a User in SSMS

Overview

This article is intended for Syniti Consultants to guide them in granting permissions and managing users (creating or deleting) in SQL Server Management Studio (SSMS) for a tenant in ADM or Migrate (ADMM). Currently, only the Consultant role needs to be granted to users, as it grants all necessary permissions. If specific permissions or exclusions are required, please contact Syniti Support.

Prerequisites

Ensure that you use the tables below to understand:

1. The parameters of the stored procedure used for user creation

   Parameter	Datatype	Definition
   @UserName	VARCHAR	The user to be created.
   @UserEmail	VARCHAR	The email id of the user, it can be blank.
   @newuser	BIT	Indicates whether a user is: 1 - New user 0 - Existing user
   @ResetPassword	BIT	Indicates whether the password needs to be reset For an existing user: 1 - No password reset 0 - Password reset For new user: when @newuser=0 and @PasswordReset =1, only then the password is reset for the user.
   @UserRole	VARCHAR	Defines the role the user needs to be granted.

2. The combination of parameters to be used for various use cases of user creation

   Parameters and Values	Use Case
   @newuser=1, @PasswordReset=0	New User creation
   @Newuser=0, @PasswordReset=1	Existing User Password Reset
   @NewUser=0, @PasswordReset=0	Existing User, only grant role
   @NewUser=1, @PasswordReset=1	Errors Out

Instructions

1. Connect to the required SQL Server instance using SSMS for your tenant.

2. Execute the stored procedure below to create the user and grant necessary permissions:
   

   --Replace 'username' with the actual username and email ID to be created.
   
   USE [Admin]
   GO
   SET NOCOUNT ON;
   EXECUTE  [dbo].[syncosp_create_userlogin] 
      @UserName='username'
     ,@UserEmail='username@syniti.com'
     ,@NewUser=1
     ,@ResetPassword=0
     ,@UserRole='Consultant'
   GO

3. The stored procedure has to be executed separately for each user and each role. If a user needs multiple role, the stored procedure has to be executed twice as given below.
   

   --Replace 'username' with the actual username and email ID, and 'Role1','Role2' with the actual roles to be created.
   
   USE [Admin]
   GO
   SET NOCOUNT ON;
   EXECUTE  [dbo].[syncosp_create_userlogin] 
      @UserName='username'
     ,@UserEmail='username@syniti.com'
     ,@NewUser=1
     ,@ResetPassword=0
     ,@UserRole='Role1'
   GO
   SET NOCOUNT ON;
   
   -- When @Newuser=0 and @ResetPassword=0, the user is assigned to the 'Role2' and no password reset happens.
   
   EXECUTE  [dbo].[syncosp_create_userlogin] 
      @UserName='username'
     ,@UserEmail='username@syniti.com'
     ,@NewUser=0
     ,@ResetPassword=0
     ,@UserRole='Role2'
   GO

4. For user deletion, execute the following stored procedure:
   

   --Replace 'username' with the actual username and email ID to be created.
   
   SET NOCOUNT ON;
   USE [admin];
   EXECUTE  [dbo].[syncosp_delete_userlogin] 
       @UserName='username'
     ,@UserEmail='username@syniti.com'
   GO

Wait for a brief moment (five minutes) for the user to be created or deleted. The user will be prompted to change their password on first login when a new user is created or their password is updated.