Solved

using sp_AddUser

Posted on 2008-06-12
10
470 Views
Last Modified: 2008-07-28
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!

0
Comment
Question by:xenium
  • 5
  • 4
10 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 50 total points
ID: 21768785
slashes are fine to use, but you need to put the name in brackets...

[domain\userid]
0
 
LVL 14

Accepted Solution

by:
PockyMaster earned 200 total points
ID: 21768849
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
 

Author Comment

by:xenium
ID: 21769154
Thanks.. can you give me the 'other stuff' too so that the user has access to database 'dbname' :-)  
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:xenium
ID: 21769175
btw, does CREATE USER work for both SQL server 2000 & 2005?

Thanks again!
0
 
LVL 14

Assisted Solution

by:PockyMaster
PockyMaster earned 200 total points
ID: 21771635
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
 

Author Comment

by:xenium
ID: 21775906
How is 'dbname' assigned?

thanks
0
 
LVL 14

Assisted Solution

by:PockyMaster
PockyMaster earned 200 total points
ID: 21776920
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
 

Author Comment

by:xenium
ID: 21797173
thanks
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 21800980
you're welcome, happy coding!
0
 

Author Comment

by:xenium
ID: 22101770
correction to link above for CREATE USER:
http://msdn.microsoft.com/en-us/library/ms173463.aspx
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 47
hyperlink data type in SQL 3 23
SSIS with VPN COnnection 2 70
VB.NET 2008 - SQL Timeout 9 22
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now