Log Shipping Administration & .TUF File

Administering and Managing Log Shipping

Log shipping is one of four SQL Server 2005 High-availability solutions.

Other SQL Server 2005 high-availability alternatives include

                Database Mirroring

                Fail-over Clustering

                Peer-to-Peer Replication.

Note: - Database mirroring and Peer-to-Peer Replication are new technologies introduced with SQL Server 2005

Log shipping offers increased database availability and database protection by maintaining a warm standby database on another instance of SQL Server 2005.

Unlike database mirroring, a Log Shipping fail-over is not done automatically but its manual fail-over.

Factors affecting Log Shipping

The size of the transaction log backups, speed of the network, and length of time the restoration takes all play a significant role in planning and scheduling the log shipping solution.

Log Shipping Design Considerations

*       SQL Server Version: SQL Server 2005 Standard, Work group and Enterprise Editions can be used for log shipping. All servers must be configured with the same case-sensitivity settings.

*       Recovery Mode: The recovery mode of the source database on the primary server must be  configured as full or bulk-logged. Because the transaction log is a key part of log shipping, the simple recovery model cannot be used.

*       Monitor Server: The monitor server is optional. This server is important because it provides a central location for the log shipping status and alert messages.

*       Security: Sysadmin role is required on each SQL Server that will take part in the Log Shipping. The agent account used to run the backup job on the primary server must be able to read and write to the backup location.

*       Backup & Restore T Log Location: The backup storage location is used to store the transaction log backups created by the backup job. It is highly recommended to host the transaction log backups on a fault-tolerant server independent of the log shipping primary or secondary servers having enough disk space. Similarly on backup server, enough drives should be available.

 Viewing Log Shipping Reports

* Log shipping reports can be viewed from the primary, secondary, and monitor servers. However, viewing the reports from the monitor server is most effective because the monitor server contains records from both the primary and secondary servers. Viewing the log shipping report from the primary and secondary servers shows only half the data.

Log Shipping Tables

Table Name	Description
log_shipping_monitor_alert	Stores alert job ID.
log_shipping_monitor_error_detail	Stores error details for log shipping jobs.
log_shipping_monitor_history_detail	Contains history details for log shipping agents.
log_shipping_monitor_primary	Stores one monitor record for the primary database in each Log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.
log_shipping_monitor_secondary	Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.

Reasons for Log shipping Failure

§  Network Failure

§  No Disk space on Secondary Server

§  Read/Write permission removed from Log folder

§  Password Expired of SS Agent Service

§  Copy / Restore job is not running 

Prerequisite to configure Log Shipping

§  2 Servers (Primary & Secondary)

§  Recovery model should be FULL

§  SQL 2005 Enterprise / Standard edition

§  Same collation on both the Servers

§  Agent should be in Automatic Mode

§  One shared folder on primary server to keep log backups

-------------------------------------------------------------------

 Script to check Log shipping Status - SQL 2000

select p.primary_Server ,p.primary_database ,s.secondary_server, p.last_backup_file,s.last_copied_file,

s.last_restored_file 

from msdb..Log_shipping_monitor_primary p , msdb..Log_shipping_monitor_secondary S

where p.primary_database = S.primary_database

 What is .TUF file in Log Shipping?

TUF file is a Microsoft SQL Server Transaction Undo file. .TUF File contains the information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed.

A transaction undo(.TUF) file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.

.TUF File in Log Shipping

The transaction undo file contains modifications that were not committed on the source database but were in progress when the transaction log was backed up AND when the log was restored to another
database, you left the database in a state that allowed addition transaction log backups to be restored to it (at some point in the future. When another transaction log is restored, SQL Server uses data
from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are completed in the next transaction log file). Following the restore, the undo file will be re-
written with any transactions that, at that point, are incomplete.
Hope its not too geeky.

Question: In my environment there is an issue with Log shipping destination file path, I've to change the file path on the destination, I've changed and LS copy is working fine and LS restore is failing because it is trying find the .tuf file on the old path which is not exists on the destination.

I don't want to do full restore for 30+ databases, so I'm trying to update the .tuf path on msdb on destination server but I couldn't find out the path details on any of the log shipping system tables. I knew the last restored file path details can be found on
dbo.log_shipping_monitor_secondary ,dbo.log_shipping_secondary_databases
tables, updating these tables not helping to resolve my issue.

Where is the .tuf file path details on msdb?

Ans: The tuf file path is none other than the column backup_destination_directory in log_shipping_secondary on the primary server. And this will be automatically updated when you change the folder name in the LS setup page . But TUF should be available in the old directory when the next restore happens.

SELECT backup_destination_directory FROM dbo.log_shipping_secondary

If you are changing the path for this directory what SQL server does is , when the next restore happens it first tries to copy the TUF file from the old directory to new directory and then only go ahead with the restore operation . If SQL server cannot find the .tuf file in the old directory or the old directory is itself lost – then there is no other way than reconfiguring your LS setup from scratch.


What is Undo File? Why it is required?

Undo file is needed in standby state because while restoring the log backup, uncommitted transactions will be recorded to the undo file and only committed transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommitted transactions from undo file and check with the new tlog backup whether the same is committed or not. If its committed the transactions will be written to disk else it will be stored in undo file until it gets committed or rolled back.

DBCC Commands Daily using Commands and Examples

DBCC Commands Daily using Commands and Examples 

1. xp_fixeddrives  ----- how much free space for all the drives. 

2. dbcc sqlperf(logspace)--- how much logspace and how much logspace used for all the database. 

3. dbcc checkdb(satyamdb) --- checkdb found allocation errors and consistency errors in particular database. 

4. dbcc updateusage(satyamdb,'dbo.t2') -- report and correct page and row count inaccuracies in catlog views. 
use satyamdb

5.DBCC SHOWCONTIG('t3','t3_indexs') -- Displays fragmentation information for the data and indexes of the specified table.
 in this result logical scan fragmentation and extent scan fragmentation values 0 -- 5 ignore, 5 -- 30 reorganization , 30+ rebuild the indexs. 

6.dbcc indexdefrag('satyamdb','t3','t3_indexs') -- command defragments the index rather than rebuilding it. This command is normally used when time is an issue, such as in cases of very large databases. What’s normally done here is that this command is run during the week, and the DBCC DBREINDEX is run once a week.These commands perform such tasks as enabling row-level locking or removing a dynamic-link library (DLL) from memory. 

7.DBCC OPENTRAN(satyamdb)with tableresults -- Display information about the oldest active transaction and the oldest replicated transactions. 

8. dbcc dbreindex('t3','t3_indexs',0) --- default fillfactor is 0 and rebuilds the indexes on a database.A fill factor is how much room to fill up the index before SQL allocates more space to the index. 
sp_who -- it display all the spid values and particular databases.

9.dbcc inputbuffer (59) -- Display the last statement sent from a client to a database instance. 

10. dbcc outbuffer(59) 

11. dbcc traceon(1204) -- Enable or Disable trace flags 


12. DBCC DROPCLEANBUFFERS -- Remove all clean buffers from the buffer pool.  

CXPACKET Wait in SQL Server

CXPACKET Wait in SQL Server

CXPacket wait in sql server occurs during parallel query execution, when a session is waiting on a parallel process to complete.

MSDN says that CXPACKET "Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem". Whilst this is true, it's also often the case that CXPACKET waits are the result of inefficient queries or stored procedures.

Here is an example of a SQL Server instance with high CXPACKET wait:

In this real-life example, several stored procedures had high CXPACKET waits because they included inneficient SQL statements which were not correctly indexed. A typical well-tuned database instance would not parallelize a query unless there was a missing index or there is an incomplete WHERE clause etc.

Potential Solutions to CXPACKET Wait

To resolve long CXPACKET waits you first of all need to establish:

1. Is the problem related to inefficient SQL which can be tuned?

Use a tool such as DBTuna to quickly find out which stored procedures or batches are taking the time, and which have high CXPACKET wait. Once these have been identified, drill-down to establish which individual SQL's the wait is on. Once isolated, use a tool such as the SQL Server Index Tuning Wizard to check for missing indexes, or out of date statistics. Fix if possible. This was the process used to solve the above real-life example. The top stored procedure included multiple select statements, but just one was the bottleneck which included an unindexed sub-query.

2. If the problem cannot be tuned with Indexing

If the statement cannot be tuning using normal mechanisms e.g. Indexing, re-writing etc. then it may be that the solution is to turn off parallelism, either for an individual query or for the whole server.

To find out the current configuration of parallelism you can run the following command: sp_Configure "max degree of parallelism".

If max degree of parallelism = 0, you might want to turn off parallelism completely for the instance by setting max degree of parallelism to 1. You could also limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 4 processors, set max degree of parallelism to 2.

See the MSDN page for more information on CXPACKET and the Max Degree of Parallelism.

Differences between 2005 and 2008

Differences between 2005 and 2008

Some random collection of daily use scripts for SQL Server DBA

http://gallery.technet.microsoft.com/Some-random-collection-of-b607bf1b

Attached are some cool scripts a DBA might find handy. This collection initiated from a forum discussion and the credit goes to Harry SQL DBA

The scripts will help you find out:

1.       Script to find out currently executing query

2.       Missing Index Script

3.       To kill existing connection

4.       To get create user script before restore

5.       To get drop user script after restore

6.       Script to check restore progress

7.       Script to check db size available space

8.       Script to script out Server Logins, database users, object permissions etc

/********************************************** 

--Script to find out currently executing query

**********************************************/

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

/**********************************************

--Missing Index Script

**********************************************/

SELECT TOP 25

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +

CASE

WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

GO

/**********************************************

--To kill existing connection

**********************************************/

       DECLARE @DBNAME VARCHAR(25)

       SET @DBNAME = DB_NAME()

       USE [master]

       DECLARE @KILLSPID VARCHAR(10)

       DECLARE @SPID INT

       DECLARE SPID_Cr CURSOR FOR SELECT SPID FROM Master..sysprocesses WHERE DBID = DB_ID(@DBNAME)

       OPEN SPID_Cr

       FETCH NEXT FROM SPID_Cr INTO @SPID

       WHILE @@FETCH_STATUS = 0

       BEGIN

        SET @KILLSPID = 'KILL '+ CAST(@SPID AS VARCHAR(10))

        Exec (@KILLSPID)

       FETCH NEXT FROM SPID_Cr INTO @SPID

       END

       CLOSE SPID_Cr

       DEALLOCATE SPID_Cr

/**********************************************

--To get create user script before restore

**********************************************/

       DECLARE @DBNAME VARCHAR(128)

       SET @DBNAME = DB_NAME()

       DECLARE @TblUser TABLE (UserNM VARCHAR(128),GroupNM VARCHAR(128),LoginNM VARCHAR(128),DefDBNM VARCHAR(128),DefSchNM VARCHAR(128),UserID INT, SID varbinary(128)) INSERT INTO @TblUser EXEC sp_helpuser

       --Create Statement

       SELECT +'USE '+@DBNAME+' IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''+UserNM+''')

       CREATE USER ['+UserNM+'] FOR LOGIN ['+LoginNM+']

       EXEC sp_addrolemember N'''+GroupNM+''', N'''+UserNM+'''' AS UserInfo

       FROM @TblUser

       WHERE LoginNM IS NOT NULL

       AND UserNM NOT LIKE 'dbo'

       AND UserNM NOT LIKE '##%'

       AND GroupNM NOT LIKE 'public'

       UNION

       SELECT +'USE '+@DBNAME+' IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''+UserNM+''')

       CREATE USER ['+UserNM+'] FOR LOGIN ['+LoginNM+']' AS UserInfo

       FROM @TblUser

       WHERE LoginNM IS NOT NULL

       AND UserNM NOT LIKE 'dbo'

       AND UserNM NOT LIKE '##%'

       AND GroupNM LIKE 'public'

/**********************************************

--To get drop user script after restore

**********************************************/

       DECLARE @DBNAME VARCHAR(128)

       SET @DBNAME = DB_NAME()

       SET NOCOUNT ON

       DECLARE @TblUser TABLE (UserNM VARCHAR(128),

       GroupNM VARCHAR(128),

       LoginNM VARCHAR(128),

       DefDBNM VARCHAR(128),

       DefSchNM VARCHAR(128),

       UserID INT,

       SID varbinary(128))

       INSERT INTO @TblUser EXEC sp_helpuser

       SELECT DISTINCT +'IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''+UserNM+''')

       DROP USER ['+UserNM+']' AS OrphUsers

       FROM @TblUser WHERE LoginNM IS NOT NULL

       AND UserNM NOT LIKE 'dbo'

       AND UserNM NOT LIKE '##%'

       AND UserID NOT IN (SELECT schema_id FROM sys.objects)

       UNION

       SELECT +'IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'''+name+''')

       DROP SCHEMA ['+name+']' AS OrphUsers FROM sys.schemas

       WHERE schema_id NOT IN (SELECT schema_id FROM sys.objects)

       AND schema_id NOT BETWEEN 16384 AND 16393

       AND schema_id NOT BETWEEN 1 AND 4

/**********************************************

--Script to check restore progress

**********************************************/

       SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

       AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

       CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

       CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

       CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

       CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

       CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

       FROM sys.dm_exec_sql_text(sql_handle)))

       FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

/**********************************************

--Script to check db size available space

**********************************************/

       USE tempdb

       SELECT

              a.FILEID,

              CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,

              CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],

              CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],

              a.name as [DATABASENAME],

              a.FILENAME as [FILENAME]

       FROM

              dbo.sysfiles a

/**********************************************

-- Script to cript out Server Logins, dataase users, object permissions etc

**********************************************/

       SELECT  @@servername AS ServerNM,name AS UserNM,

       CASE WHEN isntname=1 THEN CASE WHEN isntgroup=1 THEN 'ADGroup' WHEN isntuser=1 THEN 'ADUser' END ELSE 'MssqlUser' END AS logintype,

       CASE WHEN sysadmin=1 THEN 'Y' ELSE 'N' END AS sysadmin,

       CASE WHEN securityadmin=1 THEN 'Y' ELSE 'N' END AS securityadmin,

       CASE WHEN serveradmin=1 THEN 'Y' ELSE 'N' END AS  serveradmin,

       CASE WHEN setupadmin=1 THEN 'Y' ELSE 'N' END AS setupadmin,

       CASE WHEN processadmin=1 THEN 'Y' ELSE 'N' END AS processadmin,

       CASE WHEN diskadmin =1 THEN 'Y' ELSE 'N' END AS diskadmin,

       CASE WHEN dbcreator=1 THEN 'Y' ELSE 'N' END AS dbcreator,

       CASE WHEN bulkadmin=1 THEN 'Y' ELSE 'N' END AS bulkadmin

       FROM master..syslogins

       WHERE name NOT LIKE '##%'

       ORDER BY name

       --database users

       --SELECT @@ServerName AS Server_NM,DB_Name() AS Database_NM,A.name AS User_NM FROM sysusers A WHERE A.name NOT LIKE 'dbo' and hasdbaccess = '1'

       --SELECT @@ServerName AS Server_NM,DB_NAME() AS Database_NM,A.name AS User_NM,B.name AS Database_Role FROM sysusers A INNER JOIN sysmembers C ON A.uid = C.memberuid INNER JOIN sysusers B ON C.groupuid = B.uid WHERE A.name NOT LIKE 'dbo'

       --sp_helpuser

       set nocount on

       DECLARE @SQL VARCHAR(2000)

       DECLARE @DBName VARCHAR(100)

       CREATE TABLE [##Tbl_sysusers] ([ServerNM] [nvarchar] (25) NULL,[DatabaseNM] [nvarchar] (100) NULL,[UserNM] [nvarchar] (100) NULL,[GroupNM] [nvarchar] (50) NULL)

       DECLARE DBNameCR CURSOR FOR SELECT name FROM master..sysdatabases

       WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

        --AND name LIKE ('Database Name')

       OPEN DBNameCR

       FETCH NEXT FROM DBNameCR INTO @DBName

       WHILE @@FETCH_STATUS = 0

       BEGIN

       SET @SQL = --'EXEC '+@DBName+'..sp_helpuser'

       'SELECT @@ServerName AS ServerNM,'''+@DBName+''' AS DatabaseNM,A.name AS User_NM,B.name AS Database_Role FROM ['+@DBName+']..sysusers A INNER JOIN ['+@DBName+']..sysmembers C ON A.uid = C.memberuid INNER JOIN ['+@DBName+']..sysusers B ON C.groupuid = B.uid

       UNION

       SELECT @@ServerName AS ServerNM,'''+@DBName+''' AS DatabaseNM,A.name AS User_NM,B.name AS Database_Role FROM ['+@DBName+']..sysusers A INNER JOIN ['+@DBName+']..sysusers B ON A.altuid = B.uid WHERE A.isaliased = 1'

       --PRINT @SQL

       INSERT INTO [##Tbl_sysusers] Exec (@SQL)

       FETCH NEXT FROM DBNameCR INTO @DBName

       END

       CLOSE DBNameCR

       DEALLOCATE DBNameCR

       SELECT ServerNM,DatabaseNM,UserNM,GroupNM FROM [##Tbl_sysusers]

       DROP TABLE [##Tbl_sysusers]

--Object Permissions

       DECLARE @SQL VARCHAR(100)

       DECLARE @DBName VARCHAR(100)

       CREATE TABLE [##Tbl_helprotect] ([Owner] [nvarchar] (128) NULL,[Object] [nvarchar] (128) NULL,[Grantee] [nvarchar] (128) NULL,[Grantor] [nvarchar] (128) NULL,[ProtectType] [nvarchar] (128) NULL,[Action] [nvarchar] (128) NULL,[Column] [nvarchar] (128) NULL)

       CREATE TABLE [##Tbl_Objects_Perm] ([ServerNM] [nvarchar] (128) NULL,[DatabaseNM] [nvarchar] (128) NULL,[Owner] [nvarchar] (128) NULL,[Grantor] [nvarchar] (128) NULL,[Grantee] [nvarchar] (128) NULL,[ObjectNM] [nvarchar] (125) NULL,[Select] [varchar] (16) NULL,[Insert] [varchar] (16) NULL,[Delete] [varchar] (16) NULL,[Update] [varchar] (16) NULL,[Reference] [varchar] (16) NULL,[Execute] [varchar] (16) NULL)

       DECLARE DBNameCR CURSOR FOR SELECT name FROM master..sysdatabases

       WHERE name NOT IN ('tempdb') AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

       --AND name like 'RealNet'

       OPEN DBNameCR

       FETCH NEXT FROM DBNameCR INTO @DBName

       WHILE @@FETCH_STATUS = 0

       BEGIN

       SET @SQL = 'EXEC '+@DBName+'..sp_helprotect'

       --PRINT @SQL

       INSERT INTO [##Tbl_helprotect] Exec (@SQL)

       --SELECT * FROM [##Tbl_helprotect] WHERE Owner NOT IN ('sys','.') AND Grantee NOT IN ('public')

       --DROP TABLE [##Tbl_helprotect]

       INSERT INTO ##Tbl_Objects_Perm

       SELECT DISTINCT @@servername AS ServerNM, @DBName AS DatabaseNM,a.Owner,a.Grantor,a.Grantee,a.Object,--g.Action END  AS [Select], b.Action AS [Insert], c.Action AS [Delete], d.Action AS [Update], e.Action AS [References],f.Action AS [Eexecute]

       CASE ISNULL(g.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE g.Action END  AS [Select],

       CASE ISNULL(b.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE b.Action END  AS [Insert],

       CASE ISNULL(c.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE c.Action END  AS [Delete],

       CASE ISNULL(d.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE d.Action END  AS [Update],

       CASE ISNULL(e.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE e.Action END  AS [References],

       CASE ISNULL(f.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE f.Action END  AS [Eexecute]

       from [##Tbl_helprotect] a

       LEFT OUTER JOIN [##Tbl_helprotect] b ON a.Object = b.Object and a.Grantee = b.Grantee and b.Action = 'Insert'

       LEFT OUTER JOIN [##Tbl_helprotect] c ON a.Object = c.Object and a.Grantee = c.Grantee and c.Action = 'Delete'

       LEFT OUTER JOIN [##Tbl_helprotect] d ON a.Object = d.Object and a.Grantee = d.Grantee and d.Action = 'Update'

       LEFT OUTER JOIN [##Tbl_helprotect] e ON a.Object = e.Object and a.Grantee = e.Grantee and e.Action = 'References'

       LEFT OUTER JOIN [##Tbl_helprotect] f ON a.Object = f.Object and a.Grantee = f.Grantee and f.Action = 'Execute'

       LEFT OUTER JOIN [##Tbl_helprotect] g ON a.Object = g.Object and a.Grantee = g.Grantee and g.Action = 'Select'

       WHERE a.Owner NOT IN ('sys','.') AND a.Grantee NOT IN ('public','guest') --a.Owner NOT IN ('.')

       DELETE FROM ##Tbl_helprotect

       FETCH NEXT FROM DBNameCR INTO @DBName

       END

       CLOSE DBNameCR

       DEALLOCATE DBNameCR

       SELECT * FROM [##Tbl_Objects_Perm]

       DROP TABLE [##Tbl_helprotect]

       DROP TABLE [##Tbl_Objects_Perm]

--Object Permissions 2

       -- note: this is sample script to get object level permissions for a particular db. check where condition

       DECLARE @SQL VARCHAR(100)

       DECLARE @DBName VARCHAR(100)

       CREATE TABLE [##Tbl_helprotect] ([Owner] [nvarchar] (128) NULL,[Object] [nvarchar] (128) NULL,[Grantee] [nvarchar] (128) NULL,[Grantor] [nvarchar] (128) NULL,[ProtectType] [nvarchar] (128) NULL,[Action] [nvarchar] (128) NULL,[Column] [nvarchar] (128) NULL)

       CREATE TABLE [##Tbl_Objects_Perm] ([ServerNM] [nvarchar] (128) NULL,[DatabaseNM] [nvarchar] (128) NULL,[Owner] [nvarchar] (128) NULL,[Grantor] [nvarchar] (128) NULL,[Grantee] [nvarchar] (128) NULL,[ObjectNM] [nvarchar] (125) NULL,[Select] [varchar] (16) NULL,[Insert] [varchar] (16) NULL,[Delete] [varchar] (16) NULL,[Update] [varchar] (16) NULL,[Reference] [varchar] (16) NULL,[Execute] [varchar] (16) NULL)

       DECLARE DBNameCR CURSOR FOR SELECT name FROM master..sysdatabases

       WHERE name NOT IN ('tempdb') AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

       --AND name like 'Database Name1' --AND name like 'Database Name 2'

       OPEN DBNameCR

       FETCH NEXT FROM DBNameCR INTO @DBName

       WHILE @@FETCH_STATUS = 0

       BEGIN

       SET @SQL = 'EXEC '+@DBName+'..sp_helprotect'

       --PRINT @SQL

       INSERT INTO [##Tbl_helprotect] Exec (@SQL)

       --SELECT * FROM [##Tbl_helprotect] WHERE Owner NOT IN ('sys','.') AND Grantee NOT IN ('public')

       --DROP TABLE [##Tbl_helprotect]

       INSERT INTO ##Tbl_Objects_Perm

       SELECT DISTINCT @@servername AS ServerNM, @DBName AS DatabaseNM,a.Owner,a.Grantor,a.Grantee,a.Object,--g.Action END  AS [Select], b.Action AS [Insert], c.Action AS [Delete], d.Action AS [Update], e.Action AS [References],f.Action AS [Eexecute]

       CASE ISNULL(g.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE g.Action END  AS [Select],

       CASE ISNULL(b.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE b.Action END  AS [Insert],

       CASE ISNULL(c.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE c.Action END  AS [Delete],

       CASE ISNULL(d.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE d.Action END  AS [Update],

       CASE ISNULL(e.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE e.Action END  AS [References],

       CASE ISNULL(f.Action,'Yes')WHEN 'Yes' THEN 'O' ELSE f.Action END  AS [Eexecute]

       from [##Tbl_helprotect] a

       LEFT OUTER JOIN [##Tbl_helprotect] b ON a.Object = b.Object and a.Grantee = b.Grantee and b.Action = 'Insert'

       LEFT OUTER JOIN [##Tbl_helprotect] c ON a.Object = c.Object and a.Grantee = c.Grantee and c.Action = 'Delete'

       LEFT OUTER JOIN [##Tbl_helprotect] d ON a.Object = d.Object and a.Grantee = d.Grantee and d.Action = 'Update'

       LEFT OUTER JOIN [##Tbl_helprotect] e ON a.Object = e.Object and a.Grantee = e.Grantee and e.Action = 'References'

       LEFT OUTER JOIN [##Tbl_helprotect] f ON a.Object = f.Object and a.Grantee = f.Grantee and f.Action = 'Execute'

       LEFT OUTER JOIN [##Tbl_helprotect] g ON a.Object = g.Object and a.Grantee = g.Grantee and g.Action = 'Select'

       WHERE a.Owner NOT IN ('sys','.') AND a.Grantee NOT IN ('public','guest') --a.Owner NOT IN ('.')

       and @DBName = 'NOA01PDB'

       DELETE FROM ##Tbl_helprotect

       FETCH NEXT FROM DBNameCR INTO @DBName

       END

       CLOSE DBNameCR

       DEALLOCATE DBNameCR

       SELECT * FROM [##Tbl_Objects_Perm]

       DROP TABLE [##Tbl_helprotect]

       DROP TABLE [##Tbl_Objects_Perm]

--User and Group Permissions

       set nocount on

       DECLARE @SQL VARCHAR(2000)

       DECLARE @DBName VARCHAR(100)

       CREATE TABLE [##Tbl_sysusers] ([ServerNM] [nvarchar] (25) NULL,

       [DatabaseNM] [nvarchar] (100) NULL,[UserNM] [nvarchar] (100) NULL,[GroupNM] [nvarchar] (50) NULL,

       [isSQLRole]  [nchar]    (1)   NULL)

       DECLARE DBNameCR CURSOR FOR SELECT name FROM master..sysdatabases

       OPEN DBNameCR

       FETCH NEXT FROM DBNameCR INTO @DBName

       WHILE @@FETCH_STATUS = 0

       BEGIN

       SET @SQL = --'EXEC '+@DBName+'..sp_helpuser'

       'SELECT @@ServerName AS ServerNM,'''+@DBName+''' AS DatabaseNM,A.name AS User_NM,B.name AS Database_Role, A.issqlrole as isSQLRole FROM ['+@DBName+']..sysusers A INNER JOIN ['+@DBName+']..sysmembers C ON A.uid = C.memberuid INNER JOIN ['+@DBName+']..sysusers B ON C.groupuid = B.uid

       UNION

       SELECT @@ServerName AS ServerNM,'''+@DBName+''' AS DatabaseNM,A.name AS User_NM,B.name AS Database_Role, A.issqlrole as isSQLRole FROM ['+@DBName+']..sysusers A INNER JOIN ['+@DBName+']..sysusers B ON A.altuid = B.uid WHERE A.isaliased = 1'

       --PRINT @SQL

       INSERT INTO [##Tbl_sysusers] Exec (@SQL)

       FETCH NEXT FROM DBNameCR INTO @DBName

       END

       CLOSE DBNameCR

       DEALLOCATE DBNameCR

       SELECT ServerNM,DatabaseNM,UserNM,GroupNM, isSQLRole FROM [##Tbl_sysusers]

       DROP TABLE [##Tbl_sysusers]