Easy query

Suppose I have a user name called "Tom" and a database called "testDB"
How can I add the user to the database by query?

I have tried "exec sp_adduser Tom, testDB", but it gives me
"[Microsoft][ODBC SQL Server Driver][SQL Server][ The Login already has
an account under a different user name".

why is it?

Brian
BrianKwanAsked:
Who is Participating?
 
chigrikConnect With a Mentor Commented:
Read about sp_addlogin from SQL Server Books Online. This is correct syntax:

sp_addlogin 'you_login', 'you_password', 'pubs'
GO
sp_adduser 'you_login', 'Tom'
GO
sp_addrolemember 'db_owner', 'Tom'
GO
SELECT name FROM sysusers WHERE name = 'Tom'
GO

This is the results set:

"
New login created.
Granted database access to 'you_login'.
'Tom' added to role 'db_owner'.
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
Tom

(1 row(s) affected)
"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the login TOM has already a user (whatever name) in the given database, you won't be able to add him a second time.
If you can't see the user TOM in the database, this may result from a backup of your database from a different server. you may then use the sp_dropuser command first.
Hope this helps
0
 
ahoorCommented:
You have to execute sp_adduser in the target database and supply the username and loginname.

So to go further on chigrik's answer:

use master
go
sp_addlogin 'Tom','password', 'testDB'
go
use testDB
go
sp_adduser 'Tom','Tom'
go

You may be able to skip the addlogin if it already exists...
the use database  is the main issue I guess.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.