question

pits avatar image
pits asked

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,
securityloginuser
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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?
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pits avatar image pits commented ·
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?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
yes - that's the one
0 Likes 0 ·
pits avatar image pits commented ·
thanks Kev, is there any command "force all users"...ect you aware of?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
no - but if it was all the user databases on the instance, then a solution like @WilliamD is suggesting would work
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.