Blog

Written By Sharita Farhana

Want to run a script in all databases in your server? Here is a quick stored procedure to do that.  Here is a sample stored procedure call. 

EXEC RunScript 'SELECT TOP 1 * FROM abc'

CREATE PROCEDURE [dbo].[RunScript]

        @Script NVARCHAR(MAX)

AS

BEGIN

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRY

       BEGIN TRANSACTION;

              IF OBJECT_ID('tempdb.[dbo].[#DBName]') IS NOT NULL DROP TABLE tempdb.[dbo].[#DBName]      

              -- Create a temp table to house the database names

              SELECT

                     [Name] as DBName,

                     ROW_NUMBER()OVER(PARTITIONBY 1 ORDERBYname) AS RowNum

              INTO #DBName

              FROM

                     sys.databases

              WHERE

                     [Name] NOT IN ('master','model','msdb','ReportServer','ReportServerTempDB','tempdb')

              ORDER BY [Name]

              DECLARE      @Db AS VARCHAR(50),

                           @I AS INT= 1,

                           @Exec AS NVARCHAR(MAX),

                           @Sql AS NVARCHAR(MAX)

                     WHILE( @i <=(SELECT MAX(RowNum) FROM #DBName))

                     BEGIN

                           SET @DB =(SELECT DBName FROM #DBName WHERE RowNum= @i);

                           SET @Exec =QUOTENAME(@Db)+N'.sys.sp_executesql';

                           SET @Sql =  N'PRINT ''Running script on ''+ DB_NAME();'+ @Script   

                           --SELECT @Script

                           EXEC @Exec @Sql;          

                           SET @I=@I+1  

                     END

       DROP TABLE #DBName

COMMIT TRANSACTION;

  END TRY

   BEGIN CATCH

       IF @@TRANCOUNT> 0

           ROLLBACK TRANSACTION;

       THROW;

   END CATCH;

END

By Sharita Farhana

The following query iterates through all databases and produce a list of users, create and modified date and their roles.

DROPTABLEIFEXISTS #Users;

CREATETABLE #Users

(

   DBName VARCHAR(100)

  ,UserName   VARCHAR(500)

  ,Create_DateDATETIME

  ,Modify_DateDATETIME

  ,Type       VARCHAR(500)

  ,Authentication_TypeVARCHAR(500)

  ,[Permission_Role_Name] VARCHAR(500)

);

DECLARE @dbname NVARCHAR(255), @sql NVARCHAR(max)

DECLARE c CURSORFORWARD_ONLYREAD_ONLYFOR

SELECT [name]

FROM

sys.databases

WHERE database_id > 4;

OPEN c

FETCHNEXTFROM c INTO @dbname ;

WHILE@@fetch_status= 0

BEGIN

    set @sql =

    'use ['+@dbname+']

    INSERT INTO  #Users

SELECT DB_NAME() as DBName,

          p.name AS username,

          p.create_date,

          p.modify_date,

          p.type_desc AS type,

          p.authentication_type_desc AS authentication_type,

          pe.name AS [Permission_Role_Name]

FROM

 sys.database_principals ASp

INNER JOIN sys.sysusers    AS u

ON P.NAME=U.name

LEFT OUTER JOIN      sys.database_role_members AS rm

ON rm.member_principal_id=p.principal_id

LEFT OUTER JOIN      sys.database_principals    AS pe

ON pe.principal_id=rm.role_principal_id

WHERE

         p.type NOT IN(''A'',

                                  ''G'',

                                  ''R'',

                                  ''X'')

         AND p.sid   IS NOT NULL

         AND p.name NOT IN(''public'',

                                         ''dbo'',

                                         ''guest'',

                                         ''INFORMATION_SCHEMA'',

                                         ''sys'',

                                         ''db_owner'',

                                         ''db_accessadmin'',

                                         ''db_securityadmin'',

                                         ''db_ddladmin'',

                                         ''db_backupoperator'',

                                         ''db_datareader'',

                                         ''db_datawriter'',

                                         ''db_denydatareader'',

                                         ''db_denydatawriter'')

ORDER BY

               UserName;'

    EXEC (@sql);

    FETCHNEXTFROM c INTO @dbname;

END

CLOSE C

DEALLOCATE c

SELECT

*

FROM

#Users

--WHERE

--Permission_Role_Name IS NOT NULL

ORDERBY

UserName,dbName