How to Fix “is not a valid login or you do not have permission” When Adding SQL Server Users

When attempting to create a new database user in SQL Server Management Studio (SSMS), you might encounter the frustrating error: "is not a valid login or you do not have permission". This typically occurs when:

The login you're trying to map doesn't exist at server level
Your current login lacks sufficient permissions
There's a mismatch between authentication modes

Before proceeding, verify these essential requirements:

-- Check if the login exists at server level
SELECT name FROM sys.server_principals WHERE type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN');

-- Verify your current permissions
SELECT HAS_PERMS_BY_NAME(NULL, 'SERVER', 'CREATE ANY USER') AS HasCreateUserPermission;

1. Create the Server Login First

Database users must map to existing server logins. For a SQL authenticated user:

USE [master]
GO
CREATE LOGIN [NewUser] WITH PASSWORD=N'StrongPassword123!', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=ON
GO

2. Grant Appropriate Permissions

If you're not a sysadmin, ensure you have these minimum permissions:

GRANT ALTER ANY USER TO [YourLogin];
GRANT IMPERSONATE ANY LOGIN TO [YourLogin];

3. Create the Database User Properly

Now create the database user with proper syntax:

USE [YourDatabase]
GO
CREATE USER [NewUser] FOR LOGIN [NewUser]
WITH DEFAULT_SCHEMA=[dbo]
GO

Windows Authentication Issues

For Windows accounts, use the correct domain format:

CREATE USER [DOMAIN\username] FOR LOGIN [DOMAIN\username];

Orphaned User Resolution

If the login exists but mapping fails:

EXEC sp_change_users_login 'Auto_Fix', 'NewUser';

Here's a complete script that checks conditions and creates both login and user:

DECLARE @LoginName NVARCHAR(128) = 'NewUser';
DECLARE @Password NVARCHAR(128) = 'StrongPassword123!';
DECLARE @DatabaseName NVARCHAR(128) = 'YourDatabase';

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName)
BEGIN
    EXEC('CREATE LOGIN [' + @LoginName + '] WITH PASSWORD=N''' + @Password + ''', 
    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON');
END

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
    EXEC('USE [' + @DatabaseName + ']; 
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @LoginName + ''')
    CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '];');
END

Required Permission	Scope	Description
CREATE ANY USER	Database	Allows creating users in specific database
ALTER ANY USER	Database	Allows modifying existing users
IMPERSONATE ANY LOGIN	Server	Required for login mapping

When attempting to create a new database user in SQL Server 2012 through SSMS, you might encounter this permission-related error even when you're logged in with what appears to be sufficient privileges. The error typically occurs at the moment of clicking "OK" in the New User dialog.

Before creating a database user, these conditions must be met:

A server login must exist in the master database
Your current login needs ALTER ANY USER permission
The target database must be accessible to your login

Here's the proper sequence to add a new database user:

First, verify if the login exists:

SELECT name FROM sys.server_principals WHERE type IN ('S','U','G') 
AND name = 'YourLoginName';

If missing, create the server login first:

USE [master]
GO
CREATE LOGIN [YourLoginName] WITH PASSWORD = 'ComplexP@ssw0rd!'
GO

Then create the database user with proper mapping:

USE [YourDatabase]
GO
CREATE USER [YourUserName] FOR LOGIN [YourLoginName]
GO

For Windows authentication users:

CREATE USER [DOMAIN\username] FOR LOGIN [DOMAIN\username];

If you need to create both login and user in one script:

USE [master]
GO
CREATE LOGIN [NewLogin] WITH PASSWORD = 'P@ssw0rd123';
GO
USE [TargetDB]
GO
CREATE USER [NewUser] FOR LOGIN [NewLogin];
GO

If you still face permission issues, check your effective permissions:

SELECT HAS_PERMS_BY_NAME(null, null, 'ALTER ANY USER');

To grant the necessary permission:

USE [master]
GO
GRANT ALTER ANY LOGIN TO [YourAdminAccount];
GO

Attempting to create a database user before the server login exists
Mismatched names between login and user
Insufficient permissions in the target database
Orphaned users causing conflicts

For orphaned user resolution:

USE [YourDatabase]
GO
EXEC sp_change_users_login 'Report'; -- First identify orphans
EXEC sp_change_users_login 'Auto_Fix', 'YourUserName';

ServerDevWorker