DB Login for SQL 2000 Server

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » DB Login for SQL 2000 Server

DB Login for SQL 2000 Server

Rate Topic

Display Mode

Topic Options

Author

Message

ridzuan_first

Posted Wednesday, November 18, 2009 5:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 05, 2010 1:50 AM
Points: 28, Visits: 183

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

Post #820745

Greg Charles

Posted Wednesday, November 18, 2009 9:02 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 3,621, Visits: 2,891

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

Post #820915

Joie Andrew

Posted Wednesday, November 18, 2009 1:53 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 10:31 AM
Points: 253, Visits: 377

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"

Post #821171

ridzuan_first

Posted Friday, November 20, 2009 7:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 05, 2010 1:50 AM
Points: 28, Visits: 183

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

Thanks,
Dwen

Post #822811

Joie Andrew

Posted Sunday, November 22, 2009 10:39 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 10:31 AM
Points: 253, Visits: 377

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"

Post #823090

Joie Andrew

Posted Sunday, November 22, 2009 10:42 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 10:31 AM
Points: 253, Visits: 377

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"

Post #823092

ridzuan_first

Posted Sunday, November 22, 2009 11:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 05, 2010 1:50 AM
Points: 28, Visits: 183

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

Post #823104

Joie Andrew

Posted Monday, November 23, 2009 1:32 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 10:31 AM
Points: 253, Visits: 377

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"

Post #823490

ridzuan_first

Posted Tuesday, November 24, 2009 5:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 05, 2010 1:50 AM
Points: 28, Visits: 183

Thank you guys, thank you! ...
Dwen ....

Post #823781

« Prev Topic | Next Topic »

Permissions