Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-22
4
Medium Priority
?
1,266 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: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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

OfficeMate Freezes on login or does not load after login credentials are input.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…

886 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