Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Create a login in SQL 2000

Author  Topic 

allan8964
Posting Yak Master

249 Posts

Posted - 2014-10-14 : 10:29:27
Hi there,

I need create a new login with some permissions in a SQL 2000 server. I know I can do the same thing in SQL 2008R2 with following code:

CREATE LOGIN MyName WITH PASSWORD = 'password';
GO

USE DBTEST;
CREATE USER MyName FOR LOGIN MyName;
GO

GRANT SELECT, DELETE, EXECUTE, INSERT TO MyName;
GO

But these won't work in SQL 2000. I use sp_addlogin, sp_adduser but I need some script to grant permission of select, delete and insert. How can I do that?
Thanks in advance.

allan8964
Posting Yak Master

249 Posts

Posted - 2014-10-14 : 11:46:09
I did some test and I found that after running sp_addlogin, sp_adduser just run GRANT and it adds the permissions:

GRANT SELECT, DELETE, INSERT ON DBTEST TO MyName;

Similar to 2008R2 format.
Go to Top of Page

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-10-15 : 03:02:35
To create a db_owner login for the empty databases for the schema repository and user databases:
  • Click Start > Programs > Microsoft SQL Server 7.0 > Enterprise Manager or Programs > Microsoft SQL Server > Enterprise Manager.

  • In the left pane of the Enterprise Manager, expand the Security folder. Right-click Logins and click New Login to create a new user for the schema repository database in the SQL Server Login Properties - New Login window.

  • On the General page, select SQL Server Authentication. Rational® ClearQuest® requires a SQL Server login for each database and does not support Windows NT® authentication. You must configure the SQL Server to operate in mixed mode (that is, Windows® NT Authentication and SQL Server Authentication). To set this property by using Enterprise Manager, right-click the server and select Properties. In the Properties window, on the Security page, click Windows NT Authentication and SQL Server Authentication.

  • On the General page, type a user name and password in the Name and Password fields. In the Defaults area, select your database and leave the Language set to Default.

  • On the Server Roles page, verify that no server roles are granted on the Server Role list.

  • On the Database Access page, select the new database from the Permit list to grant the login permission. Under Permit in database role, select db_owner and public. Do not grant the user any other privileges.

  • Click OK and verify your password.
    Repeat Step 1 to Step 7 for each Rational ClearQuest user database that you have created. When repeating Step 2, double-click the user name that you created for the schema repository in the right pane of the Enterprise Manager. On the General page, the Name and Password fields are populated.
Go to Top of Page
   

- Advertisement -