question

moondaddy-8531 avatar image
0 Votes"
moondaddy-8531 asked moondaddy-8531 commented

SQL Server error assigning windows user to database

Using sql server 2019 I have a windows user and want to assign it to a database. but no mater which which windows user I use, I get an error

 Create failed for User 'xxx-xxx\sysadmin'.
 'xxx-xxx\sysadmin' is not a valid name because it contains invalid characters.

Where "xxx-xxx" is the machine name created by the datacenter.

It seems odd that I can't assign a windows user to the db. Any solutions?

Thanks.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi,
You show the error message but not what you tried to execute. Please provide the queries which you use for the task

0 Votes 0 ·

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

Using SSMS, not scripts, database/security/users, select "New User". User type = Windows user, Select a windows user "machinename\username, Login name = username, default schema = dbo, click OK

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered moondaddy-8531 commented

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

While unlikely to be related to this problem, you should apply the most recent Cumulative Update which is CU13 to get access to many bug fixes in SQL 2019.

Using SSMS, not scripts, database/security/users, select "New User". User type = Windows user, Select a windows user "machinename\username, Login name = username, default schema = dbo, click OK

I tried this and it worked for me with SSMS 18.10. Which version of SSMS do you have? Did you try the Script button to see what SSMS actually generates? It would help to see a screenshot.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @ErlandSommarskog , ssms v18.10

Script:
USE [arcgistest]
GO
CREATE USER [58942-14121\arcgis] FOR LOGIN [arcgis] WITH DEFAULT_SCHEMA=[dbo]
GO

Error message:
TITLE: Microsoft SQL Server Management Studio



Create failed for User '58942-14121\arcgis'. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476


ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)



'58942-14121\arcgis' is not a valid name because it contains invalid characters. (Microsoft SQL Server, Error: 15006)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-15006-database-engine-error


BUTTONS:

OK


0 Votes 0 ·

That would need to read

CREATE USER [58942-14121\arcgis] FOR LOGIN [58942-14121\arcgis] WITH DEFAULT_SCHEMA=[dbo]

Make sure that you enter the domain both for the user and for the login. (Or just leave the login blank.)

0 Votes 0 ·

Ahh, thank you!!!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

It would have helped if you had shown us the actual command you are using.

This command is from a demo script that I have:

CREATE USER [LIVERPOOL\SirPaul]

and that I know runs if I change LIVERPOOL to my actual machine name.

Also, post the output from SELECT @@version.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.