x

add user to the database

Hello,

I need to add one user to different 30 databases on the same instance.Is there any way to do it in less time then to add it manually one by one.

there is some command "force all users"...ect,can you please let me know the exact command if you are aware.

thank you rgds,

more ▼

asked Aug 30, 2011 at 04:56 AM in Default

avatar image

pits
830 89 92 95

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Using SSMS you can quickly check all the tick-boxes for the databases that the login requires. This is on the "Login Properties" page, "User Mapping" section.

Does this give you what you need or do you need to set more specifics about the users?

more ▼

answered Aug 30, 2011 at 05:01 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

thanks,do you mean Server- Security-Logins-user name-login properties-user mapping and tick the db for which the access needs to be given?

Aug 30, 2011 at 05:28 AM pits

yes - that's the one

Aug 30, 2011 at 05:29 AM Kev Riley ♦♦

thanks Kev, is there any command "force all users"...ect you aware of?

Aug 30, 2011 at 05:42 AM pits

no - but if it was all the user databases on the instance, then a solution like @WilliamD is suggesting would work

Aug 30, 2011 at 05:59 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Something like this may also work for you (try it out, it will print the commands that would be run):

 DECLARE @DBName varchar(255),
         @User varchar(255),
         @sql varchar(8000)
 
 SET @User = 'MyTestUser'
 
 DECLARE DBUSer CURSOR FAST_FORWARD READ_ONLY FOR
 select name 
 from sys.databases
 where state=0 --online
 and database_id>4 -- not systemdatabase
 
 OPEN DBUser
 
 FETCH NEXT FROM DBUser INTO @DBName
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
 
 SET @sql = 'USE '+@DBName +' GO '+'CREATE USER '+ QUOTENAME(@User) + ' FOR LOGIN ' + QUOTENAME(@User) + ' GO'
 
 PRINT @sql
 -- EXEC @sql
 
 FETCH NEXT FROM DBUser INTO @DBName
 
 
 END
 
 CLOSE DBUser
 DEALLOCATE DBUser


If this fits, you can uncomment the EXEC line and it will work. I'm going on the assumption you want the user to be called the same as the login.

more ▼

answered Aug 30, 2011 at 05:17 AM

avatar image

WilliamD
26.2k 18 34 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x198
x102
x26

asked: Aug 30, 2011 at 04:56 AM

Seen: 1186 times

Last Updated: Aug 30, 2011 at 04:58 AM

Copyright 2016 Redgate Software. Privacy Policy