Microsoft.SqlServer.Management.Smo.User UserType enum member error in powershell?

Question

I'm use follow powershell script code for create database user in Sql Server 2019 Express:

$login = "myLogin" (login and database was created early)

$db = $srv.Databases.Item("myDatabase")

$dbuser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User -ArgumentList $db, $login

$dbuser.Login= $login

i need create SQL user WITH login

$dbuser.UserType="SqlUser"

$dbuser.Create()

User was created WITHOUT login, and error " Exception calling "Create" with "0" argument(s): "Create failed for User **** " was occured

These error occured which metod Microsoft.SqlServer.Management.Smo.User.Create() was call to create user WITHOUT login because you must add password as first argument.

In this script i set Login property and UserType property right for create db user WITH login.

In documentation you can see enum UserType members description:
....
SqlLogin 0 'Specifies a SQLLogin user. This is deprecated, use SQLUser instead.

SqlUser 0 'Specifies that the user is either a SQLLogin user or a user with password.

But next script i drop this user and create again with same script.

And user was created success WITH login, without any errors!!!

I try use $dbuser.UserType="SQLLogin " but same result.

Any idea?

Thank you!

Answer 1

I know (well, remember, actually) little about SQL, but if you're only trying to create a login object shouldn't you just use the Add-SqlLogin cmdlet?

The "UserType" enum just equates a value to a name. You can usually use just the value. The "SqlLogin" and "SqlUser" names are both assigned a value of "0":

AsymmetricKey 2 Specifies that the user login for the database is based on an asymmetric key.
Certificate 1 Specifies that the user login for the database is based on a certificate.
External 4 Specifies that the user is based on external authentication
NoLogin 3 Specifies that the user does not have a login for the database.
SqlLogin 0 Specifies a SQLLogin user. This is deprecated, use SQLUser instead.
SqlUser 0 Specifies that the user is either a SQLLogin user or a user with password.

Answer 2

HI @Evgeny ,

WITHOUT LOGIN  

Specifies that the user should not be mapped to an existing login.

enum typy just is a data type that enables for a variable to be a set of predefined constants, in this document, after creating database user, one more step is to add it to a database role

It is not clear if the user you use afterwards has completed the mapping, but no matter what, it is unlikely to be associated with without login or with login

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I tryed create user and map it to exists login

As I understand it, the Smo.User.Create method is used for this with pre-initialized Smo.User.UserType='SqlLogin' and Smo.User.Login='some_login' properties ('some_login' Login must be created early)

in documentation Smo.User.UserType 'SqlLogin' member is deprecated and i used Smo.User.UserType='SqlUser'

but first call (user not exist) of Smo.User.Create is created user WITHOUT map to login
second call (after drop user) of Smo.User.Create is created user WITH map to login

Next steps, of course, will be add user to role and grant to him some permissions.