Post reply

HELP Creating LOGIN

  • February 19, 2015 at 12:35 pm

    #297856

    Hello.

    I have a situation where I need to script a login -

    The login will need to exist on several machines - I will push the script out to the appropriate servers.

    each server will have a different name of course, but the login name will be the same for each server.

    The Windows 8 user will already be created for me, so all I have to do is add the login to the SQL instance.

    The login is not part of a domain - they are Windows logins created on each server - (already done)

    In this case I am using the account named test.

    There is no Management Studio on the servers (for security reasons), so that is why I am scripting.

    I tried the following but I keep getting error:

    CREATE LOGIN [%computername%\test] FROM WINDOWS WITH DEFAULT_DATABASE=[master],

    DEFAULT_LANGUAGE=[us_english]

    GO

    STEP 2:

    ALTER SERVER ROLE [diskadmin] ADD MEMBER [%machinename%\test]

    GO

    What am I doing wrong/ Is it the variable perhaps?

    Thanks for any suggestions

    Terry

  • February 19, 2015 at 12:44 pm

    #1778748

    I assume you are calling this a variable: %computername%? Not allowed IIRC.

    Did you try with a specific name instead? Also, exactly what is the error you are getting??

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 19, 2015 at 12:51 pm

    #1778750

    CREATE LOGIN [%computername%\test] FROM WINDOWS WITH DEFAULT_DATABASE=[master],

    DEFAULT_LANGUAGE=[us_english]

    GO

    Msg 15401, Level 16, State 1, Line 1

    Windows NT user or group '%computername%\test' not found. Check the name again.

    If I hard-code the computer name, it works fine. But every machine will have a different computer name so I am trying to avoid typing

    each computer name on all 100 servers.

    Thank you......

  • February 19, 2015 at 1:29 pm

    #1778757

    dynamic sql and and a serverprooperty might help, but how do you KNOW the user test already exists ont he local machine? why cna't you just add a domain\test instead of a local?

    /*--results

    CREATE LOGIN [GDC-SQL-P200\test] FROM WINDOWS WITH DEFAULT_DATABASE=[master],

    DEFAULT_LANGUAGE=[us_english]

    */

    DECLARE @cmd varchar(max) = REPLACE('CREATE LOGIN [%computername%\test] FROM WINDOWS WITH DEFAULT_DATABASE=[master],

    DEFAULT_LANGUAGE=[us_english]','%computername%',CONVERT(varchar,Serverproperty('ComputerNamePhysicalNetBIOS')) )

    print @cmd

    --EXEC(@cmd)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 19, 2015 at 1:33 pm

    #1778759

    What about dynamic SQL? Something like this where you build the statement, changing the @computername variable each time.

    Lowell beat me into print

    That might work for you.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • February 19, 2015 at 1:36 pm

    #1778762

    WOW!

    Thank you so much Lowell.

    This is so cool. I'm still new to SQL Server and I hope to be able to do this myself one of these days!

    Thanks again - I really appreciate it....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply