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 SSChasing Mays Points: 649 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.