Sql-server – SQL Server domain name and user name formatting

sql server

I am executing a SQL script in SQL Server Management Studio 2018. In my script I need to specify a user (including the domain – unsure if I need the server name).

So I have created a user sam, set the user type to SQL user without login and set the users role to db_datareader and db_datawriter.

I then execute my script but it gives me the error: User or role 'MHT.sam' does not exist in this database.

But I am almost certain I have added this user to the database (see my images below to double check). Is my user and domain name format correct? What do you think I am doing wrong?

enter image description here

Here's my domain and server:
enter image description here

Best Answer

You are possibly mixing SQL Server Logins and Database Users.

Introduction

A SQL Server Login is an account that allows a user to connect to the SQL Server instance. A SQL Server Login can be either ...

  • a native SQL Server login which exists in the SQL Server instance
  • a Windows Account from either the local server or the Active Directory which is linked (mapped) into the SQL Server instance

Examples of such accounts are:

  • sa (native SQL Server Authenticated login)
  • NT-Authority\SYSTEM (local Windows Authenticated login)
  • MHT\sam (Active Directory Windows Authenticated login)

When you create a user in SQL Server Management Studio (SSMS) with the graphical user interface, you are initially creating the SQL Server Login (Windows authentication or SQL Server authentication).

Differences at this stage are:

  • Windows authenticated account doesn't require a password
  • SQL Server authenticated account requires a password

When you have filled in the initial information you will normally switch tabs and configure the User Mappings.

In the User Mapping tab, you are mapping the SQL Server login to a database user. In most cases you will use the same name for the database user. In other cases you might be creating a totally different name.

Important: The database user has rights/permissions in the database. The SQL Server Login has access to the SQL Server instance.

In your case...

...you are assigning rights/permissions to the database user sam. However, sam isn't aware that he is linked to the SQL Login MHT\sam and it doesn't really matter, because you assign the database permissions/rights to a database user and not to SQL Server login.

Possible Reason For Discrepancies Between sam and MHT\sam

While you (or somebody else) were creating the MHT\sam SQL Server Windows Authenticated Login and after switching to the User Mappings section, the database user was truncated to sam.

SQL Server Windows Authenticated Login | General SQL Server Windows Authenticated Login General Information This is where decide if you will be creating a native SQL Server Login or a Windows Authenticated Login.

SQL Server Windows Authenticated Login | User Mappings SQL Server Windows Authenticated Login User Mappings This is where you link the SQL Server Login to a newly created database user or where you link the SQL Server Login to an already existing database user.

Solution

exec sp_addrolemember N'RunStoredProcedure', N'sam'
go
exec sp_addrolemember N'MonitoringRole', N'sam'
go

Explanation

The SQL Server Windows Authenticated Login MHT\sam is linked to the database user sam, which has now been granted the database role RunStoredProcedure and MonitoringRole. When MHT\sam logs in to the SQL Server instance and connects to his database, he has the rights/permissions assigned to the database user sam.

Related Question