Adding a New User with Database Owner Privileges using OSQL
Technical Note 1605
Last Reviewed 19-Dec-2002
Applies To
Express Software Manager version 6.0 and higher
Summary
If you do not have a full version of Microsoft SQL Server with SQL Enterprise Manager, you can add a new user with database owner (dbo) privileges using the OSQL utility. This technical note describes how to use OSQL commands to add a user that has dbo privileges.
About the OSQL Utility and OSQL Commands
The OSQL utility comes with every copy of SQL Server, including MSDE. This utility allows you to run scripts, system procedures, and Transact-SQL statements directly from the operating system command prompt.
OSQL commands can include a large number of parameters or switches. To see a list of OSQL command options, enter the following command at the command prompt of the database server (the machine on which SQL Server or MSDE is running):
Many of the switches are case sensitive while some parts of the commands are not. Unless you have specific experience with OSQL, we recommend that you enter the commands exactly as they are presented in this note.
The OSQL utility can be run in interactive mode, or in batch mode. The instructions in this technical note specify a single command line that starts the utility, executes a query, and exits the utility afterward.
For more information about OSQL, see the Microsoft Web site.
Adding a Database User with Database Owner (dbo) Privileges using OSQL Commands
Express Software Manager requires an administrator that has database owner (dbo) privileges for the SQL database being used by Express. By default, the Express Software Manager installation program attempts to use the “sa” account. If this account is not available, you need to create a new account that has dbo privileges.
To create a new account with dbo privileges:
Log on to the computer running MSDE, using an account that has local administrative rights.
Enter each of the following case-sensitive commands from a command prompt:
osql –E -Q"Exec sp_addlogin @loginame = '<new account name>', @passwd = '<new password>'"
osql –E -Q"Exec sp_addalias '<new account name>', 'dbo'"
osql –E -Q"Exec sp_addsrvrolemember '<new account name>', 'sysadmin'"
Example
Note: The below example assumes that you are adding a new account named “sanew” with a password of “newpass”.
To create a new account with dbo privileges:
- Log on to the computer running MSDE, using an account that has local administrative rights.
- Enter the following case-sensitive command from a command prompt:
osql –E -Q"Exec sp_addlogin @loginame = 'sanew', @passwd = 'newpass'"
- Enter the following case-sensitive command from a command prompt:
osql –E -Q"Exec sp_addalias 'sanew', 'dbo'"
- Enter the following case-sensitive command from a command prompt:
osql –E -Q"Exec sp_addsrvrolemember 'sanew', 'sysadmin'"
Related Technical Notes
| 1606 |
Maintaining Your SQL Database With OSQL |
| 9989 |
Express Software Manager 6.x Technical Notes |
| 
