Posted on 2006-04-15
Last Modified: 2008-01-09
I am attempting to script out all my users so i can recover them if needed.  The problem is i don't remember all the passwords so i am attempting to use sp_addlogin with the skip_encryption option.

Here is what i have:

sp_addlogin 'username', 'hashedpassword', 'dbname', 'sid', 'skip_encryption'

and i get the following error:

Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

So, then if i try:

sp_addlogin 'username', convert(varchar, 'hashedpassword', 'dbname', 'sid', 'skip_encryption'

i get the following error:

Incorrect syntax near the keyword 'convert'.

any ideas on what i am doing wrong?
Question by:DJMoonLight
    LVL 75

    Accepted Solution

    Hi DJMoonLight,
    > sp_addlogin 'username', 'hashedpassword', 'dbname', 'sid', 'skip_encryption'

    this will do it, sp_addlogin     @loginame = 'username',
            @passwd ='hashedpassword',
                    @encryptopt= 'skip_encryption'

    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    From BOL

    sp_addlogin [ @loginame = ] 'login'
        [ , [ @passwd = ] 'password' ]
        [ , [ @defdb = ] 'database' ]
        [ , [ @deflanguage = ] 'language' ]
        [ , [ @sid = ] sid ]
        [ , [ @encryptopt = ] 'encryption_option' ]

    [@loginame =] 'login'

    Is the name of the login. login is sysname, with no default.

    [@passwd =] 'password'

    Is the login password. password is sysname, with a default of NULL. After sp_addlogin has been executed, the password is encrypted and stored in the system tables.

    [@defdb =] 'database'

    Is the default database of the login (the database the login is connected to after logging in). database is sysname, with a default of master.

    [@deflanguage =] 'language'

    Is the default language assigned when a user logs on to SQL Server. language is sysname, with a default of NULL. If language is not specified, language is set to the server's current default language (defined by the sp_configure configuration variable default language). Changing the server's default language does not change the default language for existing logins. language remains the same as the default language used when the login was added.

    [@sid =] sid

    Is the security identification number (SID). sid is varbinary(16), with a default of NULL. If sid is NULL, the system generates a SID for the new login.  Despite the use of a varbinary data type, values other than NULL must be exactly 16 bytes in length, and must not already exist. SID is useful, for example, when you are scripting or moving SQL Server logins from one server to another and you want the logins to have the same SID between servers.

    [@encryptopt =] 'encryption_option'

    Specifies whether the password is encrypted when stored in the system tables. encryption_option is varchar(20), and can be one of these values.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now