Solved

Need a script to add user to all SQL 2008 R2 databases

Posted on 2011-03-22
4
1,253 Views
Last Modified: 2012-05-11
Hello:

Been asked to create script to generate a specific dbo user for all db's in ssms 2008 R2 (and 2005). I see "user mapping" in main security folder.. (essentially same thing?)

I also see how to copy from db to db with a simple query, but Boss wants a script to run now and then that will instantly place this user in db's where not present. (I also see where if one exists it errors out anyway, so not too important to specify "IF" etc, but would be cool if not too too hard

anybody have some code or a website or three? thanks
0
Comment
Question by:Howlermunkey
  • 2
4 Comments
 
LVL 13

Expert Comment

by:agarwalrahul
Comment Utility
0
 

Author Comment

by:Howlermunkey
Comment Utility
cool, thanks for the sites, the second explains things very well. I can create users via mapping or per DB, just hoping for some kind of simple script that would allow me to select all db's.

For example, in ssms:

USE [dbname]
GO
/****** Object:  User [countchocula]    Script Date: 03/22/2011 22:44:50 ******/
GO

CREATE USER [countchocula] FOR LOGIN [countchocula] WITH DEFAULT_SCHEMA=[dbo]
GO

so i can run this per db, or go to the main security folder  and use "user mapping",  Boss wants script to run against all db's.

any ideas? there has to be something fairly simple, I just cant find it (tired hahaha).
0
 
LVL 1

Accepted Solution

by:
chandraboth earned 500 total points
Comment Utility
hi this is the PROC i wrote 5 year ago:

CREATE PROCEDURE dbo.sp_addUserPer
      @DBNAME nvarchar(200)=null
      --@BrName nvarchar(10)=''
WITH ENCRYPTION
as
      
      DECLARE LSTDB CURSOR FOR
        Select sysdb.name from sysdatabases sysdb
          where sysdb.name not in('master','msdb','tempdb','model')

      OPEN LSTDB
      
       FETCH NEXT FROM LSTDB into @DBNAME
        While (@@FETCH_STATUS = 0)
         BEGIN
            EXEC sp_ClearSysUser @DBNAME
          FETCH NEXT FROM LSTDB into @DBNAME
         END
      CLOSE LSTDB
      DEALLOCATE LSTDB

      Declare @UserLst nvarchar(80), @pwd nvarchar(10)

      Declare LSTLOGIN Cursor for
          Select 'BMUser' as [Name] union Select 'MBUser' as [Name] union Select 'ReadUser' as [Name]
      Open LSTLOGIN
      Fetch next from LSTLOGIN into @UserLst
         While (@@Fetch_status=0)
           Begin
            Exec sp_droplogin @userLst
            if (@userlst='BMUser')
               set @pwd='BM268'
            else
                begin
                  if(@userlst='MBUser')
                     set @pwd='MB628'
                  else
                    begin
                     set @PWD='Read737'
                    end
                end
            exec sp_addlogin @userlst,@pwd
      
            DECLARE LSTDB CURSOR FOR
              Select sysdb.name from sysdatabases sysdb
                  where sysdb.name not in('master','msdb','tempdb','model')
      

            OPEN LSTDB
      
             FETCH next FROM LSTDB into @DBNAME
              While (@@FETCH_STATUS = 0)
               BEGIN
                  
                  if (@userlst='ReadUser')
                    exec('use '+ @dbname + ' exec sp_adduser '+ @userlst + ','+ @userlst + ',' + 'db_datareader' + '
                        grant update on SAF to ' + @userlst +'
                          Grant update,Select,insert on TLRLOG TO '+ @userlst +'
                        Grant update,Select,insert on YLOG TO '+ @userlst +'
                        exec sp_addrolemember db_ddladmin,''ReadUser''')
                  else
                   begin
                    exec('use ' + @dbname + ' exec sp_adduser '+ @userlst + ',' + @userlst + ',' + 'db_owner')
                   end

                  exec('use ' + @dbname + '
                  declare @sql varchar(255)
                  declare grant_cur cursor for

                  select ''grant execute on ''+ name + '' to '+ @userlst +'''
                  from sysobjects
                  where xtype = ''P'' AND
                        name not like ''dt_%''
                  order by name
                  
                  open grant_cur
                  FETCH NEXT FROM grant_cur INTO @sql
                  WHILE (@@fetch_status <> -1) BEGIN
                        exec(@sql)
                        FETCH NEXT FROM grant_cur INTO @sql
                  END
                  close grant_cur
                  deallocate grant_cur')

                FETCH NEXT FROM LSTDB into @DBNAME
                END
            CLOSE LSTDB
            DEALLOCATE LSTDB
           Fetch next from LSTLOGIN into @UserLst
               End
      close lstlogin
      deallocate lstlogin

      exec sp_password '','MISDept','sa'
GO
0
 

Author Comment

by:Howlermunkey
Comment Utility
Perfect. Yep, that ones going straight to my archive :). Thanks to you both, Chandabroth gets the points, I've been looking for this code for too long, thanks buddy!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

744 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

18 Experts available now in Live!

Get 1:1 Help Now