Windows NT user or group DOMAIN \ USER not found?

Question

I am trying to create users on an SQL server from an Active Directory group, because the application I am working with does not support Windows authentication and relies on separate logins created on the SQL server, since application-level permissions are controlled in the application, rather than using roles SQL In this regard, each user who needs to access the application needs to create his own user against the SQL instance in which the application database is located, so an individual permission can be assigned to the user in the application.

I am reading a list of users from the Active Directory group that we have assigned using the following:

exec master..xp_logininfo 'domain\groupname', 'members'

Returns output similar to the following:

 account name type privilege mapped login name permission path DOMAIN\USER user user DOMAIN\USER DOMAIN\GROUPNAME 

For the most part, users returned from this list can be created on an SQL instance without any drama. I create users as SQL accounts using sp_grantlogin in the first instance before moving on to allowing each new entry to the application database. However, several users are reported as non-existent. I get the following error as a result of running sp_grantlogin ;

 Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49 Windows NT user or group 'DOMAIN\USER' not found. Check the name again. 

Obviously, in the above error message, I deleted the actual username. Why xp_logininfo return a user that cannot be created using sp_grantlogin ? Is there something obvious that I'm missing?

Answer 1

This means that the user is not in the Administrator group. If your problem is similar to mine, where your Active Directory is on a different virtual machine and your SQL Server is on a different one. And you joined the Active Directory domain on your SQL Server virtual machine, then you need to do the following on your SQL Server virtual machine.

• Go to Tools → Computer Management .

• The windows are opened, Expand System Tools → Local Users and Groups .

• Click Groups and you will see a list of groups on the right column of the window.

• Double-click Administrator , a new window will open, and you will notice that the associated user is not there.

• Click Add , a new window will open. Here, by location, you can change the location of your domain.

• Click Advanced , a login prompt will open, just log in with the administrator account of the virtual machine.

• Click Find Now with all fields as is. From the list of presented users, double-click the user imported from Active Directory and click OK .

Answer 2

Do you change the username case before using sp_grantlogin ?

If you have a case-sensitive server collation, then the case of the AD user should be indicated in the correct case.

You can find the server sort by doing:

 select serverproperty('collation') 

If you have a case sensitive server sorting and you don't mess things up, there might be a mismatch with xp_logininfo return and actual case in AD. In this case, try to create a user with changes in the case.

If this is not applicable, look in your account. This is disabled, you can log in with it, etc. If suser_sid () returns null, then there must be some kind of problem with it.

Answer 3

I can give you my advice to do this on Windows 7, although this may not be relevant.

The problem was that I renamed the user account in the Windows user interface. The name appeared correctly on Windows, and I used the new name to login. But behind the scenes, he still used the old name that SQL Server was looking for.

I fought this for HOURS before I finally worked it out!

Answer 4

I also encountered this error for users who were:

• created in AD
• granted some SQL permissions
• renamed to AD

Then I try to add this new, renamed user account name to the same server / database, error Msg 15401, level 11, state 1, sp_grantlogin procedure, line 49 appears.

I followed the steps at http://support.microsoft.com/kb/324321/en-us and this command returned the name of the old user account to rename:

 SELECT name FROM syslogins WHERE sid = SUSER_SID ('YourDomain\YourLogin') 

he returned your_domain \ OldLogin

after exec exec sp_revokelogin 'YourDomain \ OldLogin'

The problem has been fixed, now sp_grantlogin is working fine.

PS as another testing method. I suggest running sp_grantlogin remotely from another server. It can succeed.

Answer 5

I had a very similar case, the same error code 15401, but in this case, what I was doing was adding users from the Domain to the group on the server where I had SQL; so just add the group engine to SQL with the same ROLE.

 USE [master] GO CREATE LOGIN [localhost\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master] Msg 15401, Level 16, State 1, Line 3 Windows NT user or group 'localhost\Administrators' not found. Check the name again. 

Then in the PRB link : use BUILTIN \ Group to provide access to predefined Windows NT groups

I found a problem, so the solution was:

 USE [master] GO CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [sysadmin] ADD MEMBER [BUILTIN\Administrators] GO Command(s) completed successfully. 

I think it’s great to reduce the number of login accounts and have a more manageable number of users assigned to roles on the SQL server.

Answer 6

If you use non-English or use it on your computer, you may need to localize the user data that you are trying to use.

eg. [NT AUTHORITY\Network Service] on the Swedish computer [NT INSTANS\Nätverkstjänst] .

Spent hours trying to understand why BUILTIN\ , NT AUTHORITY\ , <MachineName>\ , etc. does not work.

Answer 7

My problem was login length. In the Domain\User syntax, Windows uses the so-called syntax before Windows 2000. This syntax limits the length of the username to 20 characters. You must shorten the username to the first 20 characters, and then it should work as follows:

 Domain\Abcdefghijklmnopqrstuvwxyz 

becomes

 Domain\Abcdefghijklmnopqrst