Post reply

DB Login for SQL 2000 Server

  • November 18, 2009 at 5:43 am

    #103855

    How can I add this user as system administrators by transact SQL?I hv attached db to the another server pc then create new user and password as below.

    EXEC sp_addlogin 'dwen', 'dwen', 'POSDVP', 'us_english'

    Your help is apprieciated,

    Dwen

  • November 18, 2009 at 9:02 am

    #1080972

    Use sp_addsrvrolemember to add the login to the sysadmin server role (I think that's what you're asking).

    sp_addsrvrolemember 'dwen', 'sysadmin'

    Greg

  • November 18, 2009 at 1:53 pm

    #1081195

    Just remember that if you are adding a SQL login instead of a Windows login, the database instance needs to be in Mixed Mode security.

    Joie Andrew
    "Since 1982"

  • November 20, 2009 at 7:04 pm

    #1082580

    Thanks Greg,wht i need to add for Mixed Mode security?

    Thanks,

    Dwen

  • November 22, 2009 at 10:39 pm

    #1082805

    Open up Enterprise Manager

    Navigate to the instance that you are troubleshooting

    Right-click the instance name and go to Properties

    Click the Security tab

    In the Authentication section, ensure that "SQL"

    Joie Andrew
    "Since 1982"

  • November 22, 2009 at 10:42 pm

    #1082807

    Sorry, I hit a button on accident and my first post went early.

    Open up Enterprise Manager

    Navigate to the instance that you are troubleshooting

    Right-click the instance name and go to Properties

    Click the Security tab

    In the Authentication section, ensure that "SQL Server and Windows" is selected and click OK

    Note: you will have to provide an SA password if you have not done so already

    Note 2: SQL services will have to be restarted, so downtime will be required

    Joie Andrew
    "Since 1982"

  • November 22, 2009 at 11:49 pm

    #1082817

    Thanks Joie,Can this be done via Transact-SQL?before this i manually configure from EM ....

  • November 23, 2009 at 1:32 pm

    #1083136

    Sure. Run this to set the security to mixed mode:

    USE [master]

    GO

    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

    GO

    You can then run this to verify what mode the security is set at:

    EXEC xp_loginconfig 'login mode'

    GO

    Joie Andrew
    "Since 1982"

  • November 24, 2009 at 5:07 am

    #1083369

    Thank you guys, thank you! ...

    Dwen ....

Viewing 9 posts - 1 through 8 (of 8 total)

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