Create SQL Login - vbscript - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Create SQL Login - vbscript

Author

Topic

cjwallace
Starting Member

21 Posts

Posted - 2005-10-24 : 09:00:32

Hi guys. i am new round here.

so please dont fry me.

Does anyone know how to create a SQL user with vbscript?

Many thanks

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-10-24 : 09:34:31

You will have to call a system stored procedure called sp_addlogin. After that, you will likely need to call sp_adduser from the database that you are creating the user in. Practice a bit in Query Analyzer with a throwaway user (but remember to throw him away), then the SQL Script should be easy enough to write.

cjwallace
Starting Member

21 Posts

Posted - 2005-10-24 : 09:59:21

Hello mate. Thankd for the reply.

I need to do this with vbscript

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-10-24 : 10:21:59

Where is the problem, then? What are you stuck on?

cjwallace
Starting Member

21 Posts

Posted - 2005-10-24 : 11:22:59

Hello mate.

Well i am trying to find or get some help on creating a vbscript that will create a SQL login and give it access to certain databases , and prompt the person running the script to enter a password for the user they are creating..

I have been all over google, then i found this site which i am hoping will help :-)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-25 : 00:36:21

quote:

You will have to call a system stored procedure called sp_addlogin.

1. connect to the server with permission to create login --sp_addlogin
2. grant the newly created login to access the database --sp_grantdbaccess
3. provide permissions to the login -- grant/revoke/deny statements

You can do this all in vbscript...
you just need to declare a connection variable and execute the command from there

HTH

--------------------
keeping it simple...

cjwallace
Starting Member

21 Posts

Posted - 2005-10-25 : 01:56:36

Jen:

Thanks for the reply. do you have any examples to get me started? i am still quite new to vbscripting

many thanks for you time

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-25 : 03:56:55

There are different ways... one of them is to call sp_addlogin directly

declare db as new adodb.connection, strConn as string

if trusted connection


strConn = "Provider=SQLOLEDB.1;Integrated Security = SSPI;Trusted_Connection=Yes;Security=Persist Security Info=True;Initial Catalog='" & strDB & "';Data Source='" & strServerName & "';"

else


strConn = "Provider=SQLOLEDB.1;User ID='" & strLogin & "';password='" & strPass & "';Initial Catalog='" & strDB & "';Data Source='" & strServerName & "';"

db.open strconn
db.execute "exec sp_addlogin @loginame='loginame',@passwd='password',@defdb='defaultdb',@deflanguage='defaultlanguage'"

db.close

--call the other SPs...

HTH

--------------------
keeping it simple...

cjwallace
Starting Member

21 Posts

Posted - 2005-10-26 : 13:30:54

Hi Jen.

Thanks for the reply. The above code means nothing to me at all. Do you have any sample code that i can look at and work out bits i need to add and take away. Sorry......I am still quite new to vbscript

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-10-26 : 13:41:15

quote:
Originally posted by cjwallace

Hi Jen.

Thanks for the reply. The above code means nothing to me at all. Do you have any sample code that i can look at and work out bits i need to add and take away. Sorry......I am still quite new to vbscript

Is this for school? Are you a network admin? What's this for?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-26 : 22:09:49

that's the code snippet you can play around with...

there's no other way of writing that piece of code any simpler

you can use the help file of your front end for explanations and use Books Online for the sql part

HTH

--------------------
keeping it simple...

cjwallace
Starting Member

21 Posts

Posted - 2005-10-27 : 07:42:08

i am a Network Admin. Basically i have a script that will create users for me. i would like once i have got my head around creating a sql login from vbscript to add it to my other script to make the whole process easier.

The code above makes no sence to me at present. i just need an example of a real world live script to create a sql login then i can work it out and understand it.

Hope you can both help

Many thanks

cjwallace
Starting Member

21 Posts

Posted - 2005-10-27 : 08:06:21

ok this is what i have so far.

cnstring = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=sa;Initial Catalog=Chris;Data Source=10.10.100.62"

db.open cnstring
db.Execute "exec sp_addlogin @loginame='cjw',@passwd='password',@defdb='Chris',@deflanguage='UK'"

When i run it i get

Error: Object required: 'db'

I know i am missing something but not sure what.

cjwallace
Starting Member

21 Posts

Posted - 2005-10-27 : 08:31:12

its ok i got it

I was missing

Set db = CreateObject("ADODB.Connection")

I will post back with any more issues. i am taking this baby one step at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources