how to create a new user

Question

Post reply

how to create a new user

kinnon_2000

Say Hey Kid

Points: 702
More actions
February 28, 2005 at 9:10 am

#66509

Hello, I'm trying to figure out how to create a new database user using sql rather than navigating the console. This is pretty important to me, because I work remotley, and the remote console is slowwww, while paste and f5 in query analiser would be perfect.
A check to see if the user exists would be nice too?
Have been digging around for a good couple of hours now without luck.
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 1

Hi Kinnon,

Check under "System Stored Procedures" in BOL. I think you will find them all there - sp_adduser etc, etc.

They are all in the master db....

Best regards

Have fun

Steve

We need men who can dream of things that never were.

rossc Say Hey Kid Points: 699 More actions · Answer 2

Hi

you need the following

sp_addlogin

@loginame = 'login' ,

@passwd = 'password' ,

@defdb = 'database',

@deflanguage = 'language'

you then need to grant access to databases:

change to database and

exec sp_grantdbaccess 'login'

to add user to a role

exec sp_addrolemember 'dbo','login'

Ross

Sivakumar-202760 Grasshopper Points: 10 More actions · Answer 3

Did you try the following option

EXEC sp_addlogin 'Siva', 'Kumar', 'corporate'

This creates a login to user Siva, with a password of kumar in the Database

'Corporate'. I hope this might be of some help to you.

Regards,

Siva

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Here's a few specific code snippets for your perusal:

---

--- to add a SQL user as 'dbo'

---

use master

go

exec sp_addlogin @loginame='user_name', @passwd='user_password', @defdb='user_database'

exec user_database..sp_adduser @loginame='user_name'

exec user_database..sp_addrolemember @rolename='db_owner', @membername='user_name'

go

checkpoint

go

---

--- to add a SQL user as a sysadmin

---

use master

go

exec sp_addlogin 'user_name','user_password','master'

exec sp_addsrvrolemember @loginame='user_name', @rolename='sysadmin'

go

checkpoint

go

---

--- to add an NT user as a sysadmin

---

use master

go

exec sp_grantlogin 'domain_name\user_name'

exec sp_addsrvrolemember @loginame='domain_name\user_name', @rolename='sysadmin'

go

checkpoint

go

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

kinnon_2000 Say Hey Kid Points: 702 More actions · Answer 5

Excellent. You've all been very helpfull.

Journeyman, thats the dogs cahOOnas.

Thanks.