Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-22
4
Medium Priority
?
1,264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Rahul Agarwal
ID: 35195614
0
 

Author Comment

by:Howlermunkey
ID: 35195664
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 2000 total points
ID: 35195690
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
ID: 35195734
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

719 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