Tuesday 12 November 2013
Friday 27 September 2013
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.
* 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.
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.
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.
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.
Some random collection of daily use scripts for SQL Server DBA
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]
Subscribe to:
Posts (Atom)