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

Evgeny 1 Reputation point
2021-11-17T01:45:39.107+00:00

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.
149938-clipboard01.jpg

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!!!

149946-clipboard02.jpg

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

Any idea?

Thank you!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,562 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,628 questions
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Rich Matheisen 35,191 Reputation points
    2021-11-17T02:56:45.663+00:00

    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.


  2. YufeiShao-msft 6,871 Reputation points
    2021-11-17T09:01:45.72+00:00

    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.

    No comments

  3. Evgeny 1 Reputation point
    2021-11-18T17:39:48.647+00:00

    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.

    No comments