I Need To Be The Excutor On All The Databases

A developer at work asked me to grant her the permissions that allows her to execute store procedures and view the definitions on all the databases on the server. However, there are over 100 databases on the same server, it would take ages to grant the permissions to her one database by another one. And on some database, she was already db_datareader.

Therefore I decided to create ‘db_excutor’ role with the permission of ‘excute’ and ‘view definition’ on all the  databases, and create new users on those databases that she didn’t have access to…there is my code.

/* Create a database list that the user need get access to */

IF Object_id ('tempdb..#name') IS NOT NULL

        DROP TABLE #name

  SELECT name INTO #name

   FROM master.sys.databases

   WHERE database_id NOT IN (1,2,3,4,6,163) 

 

-- SELECT * FROM #name

 DECLARE @name nvarchar(100)

       , @row int

      

 SELECT TOP 1 @name = name FROM #name

 SELECT @row = @@rowcount

 WHILE @row >0

   BEGIN

/*1. check if db_executor has been already created on db

  2. check whether the user has existed on the db */

 

    EXEC (' USE ' + @Name + ';

         

          IF NOT EXISTS (SELECT * FROM dbo.sysusers
                          WHERE name = ''db_executor'')

              BEGIN

                  EXEC sp_addrole ''db_executor'', ''dbo''

                  GRANT VIEW DEFINITION TO db_executor

                  GRANT EXECUTE TO db_executor

              END
   
          IF NOT EXISTS (SELECT * FROM dbo.sysusers
                     WHERE name = ''user'')

              BEGIN

                 CREATE USER [User] FOR LOGIN [User]

                 EXEC sp_addrolemember ''db_executor'', ''user''

             
              END

   

         ELSE

            EXEC sp_addrolemember ''db_executor'', ''user''')

   

    DELETE FROM #name WHERE name = @name

   

    SELECT TOP 1  @name = name FROM #name

    SELECT @row = @@rowcount

   END