DB Login for SQL 2000 Server

SQLServerCentral is supported by Red Gate Software Ltd.

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, June 03, 2011 7:27 AM
Points: 32, Visits: 224

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 @ 10:10 AM
Points: 3,904, Visits: 4,129

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

Old Hand

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 4:49 AM
Points: 362, Visits: 574

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, June 03, 2011 7:27 AM
Points: 32, Visits: 224

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

Old Hand

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 4:49 AM
Points: 362, Visits: 574

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

Old Hand

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 4:49 AM
Points: 362, Visits: 574

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, June 03, 2011 7:27 AM
Points: 32, Visits: 224

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

Old Hand

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 4:49 AM
Points: 362, Visits: 574

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, June 03, 2011 7:27 AM
Points: 32, Visits: 224

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

Post #823781

« Prev Topic | Next Topic »

Permissions