using sp_AddUser

hi,

i'm new to these sp's but want to use them as an alternative to using Enterprise Manager etc for assigning access rights. I need to replicate the rights of an existing user and apply to a new user. The existing looks like this: (using sp_HelpUser)

UserName:      domain\userid      
GroupName:      public            
LoginName:      domain\userid      
DefDBName:      dbname            
DefSchemaName:      dbo      
etc

What sp_Adduser command should i issue to create a new user as above?

I hesitate because the help doc says that backslash (\) is not allowed in UserName, though i see backslashes in the UserNames of existing users. I'm looking for some confidence in the right command to use so that i don;t create some mess-up that needs sorting out later.

Thanks!

xeniumAsked:
Who is Participating?
 
PockyMasterCommented:
I don't think you should be using these sp's anymore. They will be phased out:
http://msdn.microsoft.com/en-us/library/ms181422.aspx
Use CREATE USER instead.
http://msdn.microsoft.com/en-us/library/ms181422.aspx

you can add users with doman name like:
CREATE USER [domain\userid] FOR LOGIN [domain\userid] WITH DEFAULT_SCHEMA=[dbo]

use CREATE ROLE and GRANT .. to get the other stuff done

0
 
chapmandewCommented:
slashes are fine to use, but you need to put the name in brackets...

[domain\userid]
0
 
xeniumAuthor Commented:
Thanks.. can you give me the 'other stuff' too so that the user has access to database 'dbname' :-)  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
xeniumAuthor Commented:
btw, does CREATE USER work for both SQL server 2000 & 2005?

Thanks again!
0
 
PockyMasterCommented:
2005 and up, as far as I know.

in sql 2005 you can also create this kind of steps by the enterprise manager, and before you press OK or APPLY, you can press the 'SCRIPT' button, so that will show you the script that you have generated by clicking in the GUI.

in 2000 you can of course also create what you want for a user, and then generate the sql scripts and see what you just did..

granting rights:
GRANT [rolename] to [username]

0
 
xeniumAuthor Commented:
How is 'dbname' assigned?

thanks
0
 
PockyMasterCommented:
you run the scripts on a specific database, so that happens automatically.
if you like to specify it, you can put the use [dbname] on top of your script
0
 
xeniumAuthor Commented:
thanks
0
 
PockyMasterCommented:
you're welcome, happy coding!
0
 
xeniumAuthor Commented:
correction to link above for CREATE USER:
http://msdn.microsoft.com/en-us/library/ms173463.aspx
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.