Thursday, April 13, 2017

T-SQL to List all Permissions for a Given Role

This script lists all the permissions for a given role.

SELECT DB_NAME() AS DatabaseName
      , AS PrincipalName
      ,DatabasePrincipals.type_desc AS PrincipalType
      , AS GrantedBy
      ,DatabasePermissions.permission_name AS Permission
      ,DatabasePermissions.state_desc AS StateDescription
      ,SCHEMA_NAME(SO.schema_id) AS SchemaName
      ,SO.Name AS ObjectName
      ,SO.type_desc AS ObjectType
  FROM sys.database_permissions DatabasePermissions LEFT JOIN sys.objects SO
    ON DatabasePermissions.major_id = so.object_id LEFT JOIN sys.database_principals DatabasePrincipals
    ON DatabasePermissions.grantee_principal_id = DatabasePrincipals.principal_id LEFT JOIN sys.database_principals DatabasePrincipals2
    ON DatabasePermissions.grantor_principal_id = DatabasePrincipals2.principal_id
WHERE = 'Test' -- Change the Role Name

You can download the script from here.
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Thursday, April 6, 2017

T-SQL to get Get Database Files Usage information

Here is an handy T-SQL script to get Database files usage Information
DECLARE @FreeSpaceThreshold int
SET @FreeSpaceThreshold = 20 -- Change this Threshold Value. Any number between 0 to 100

 ServerName nvarchar(100),
 DatabaseName nvarchar(100),
 LogicalFileName nvarchar(100),
 FileType nvarchar(10),
 FileLocation nvarchar(max),
 [FileSize(MB)] decimal (15,2),
 [UsedSpace(MB)] decimal (15,2),
 [FreeSpace(MB)] decimal (15,2),
 [UsedSpace(%)] decimal (15,2),
 [FreeSpace(%)] decimal (15,2)

EXEC sp_MSforeachdb 'Use [?];
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName, 
    type_desc AS FileType, 
    physical_name AS FileLocation,
    CAST(size/128.0 AS decimal(15,2)) AS [FileSize(MB)],
    CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [UsedSpace(MB)],
    CAST(size/128.0 AS decimal(15,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [FreeSpace(MB)],
    CAST(((FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [UsedSpace(%)],
    CAST((((size/128.0) - FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [FreeSpace(%)]
FROM sys.database_files'

SELECT * FROM #GetDetails Where [FreeSpace(%)] < @FreeSpaceThreshold
DROP TABLE #GetDetails

Download this script from

Sunday, March 12, 2017

T-SQL script to get Backup Details of a Database and the Sequence it was taken

Here is an handy script which give the details of the list of backup(s) taken of a database and the sequence it was taken.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = '<DB Name>'

 BS.server_name AS [Server Name]
,BS.database_name AS [Database Name]
,BS.recovery_model AS [Recovery Model]
,BMF.physical_device_name [Location]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = @db_name
ORDER BY backup_start_date DESC , backup_finish_date

Download the script from

Monday, January 16, 2017

Kernel for SQL Database - Product Review - A guest Post by Andre Williams

Most of the organizations that handle large amount of data is very much dependent on MS SQL Server for their operations. However, an MS SQL database corruption, can cause a lot of troubles to the organization owing to the criticality of the data contained in the database. Fortunately, many SQL Server recovery tools are available in the market. We will try to understand the features, working, and benefits & drawbacks of a popular, SQL recovery tool—the Kernel for SQL Database.

Product information
  • Name - Kernel for SQL Database
  • Description – MDF file recovery software (recovers SQL data from corrupt databases)
  • Developers – Lepide Software
Company information 
Lepide Software, the developers of Kernel range of products, is known for sophisticated IT security, management, and recovery solutions. Its Kernel range, designed and developed for small and mid-sized organizations offers cost-effective solutions for most of the day-to-day issues faced by IT organizations. These products help organizations in email migration, email recovery, data recovery, file repair, and more. The company has a loyal customer-base spanning across the world owing to its quality products and dedicated customer support.

Notable features of the software
  • Automatically detects the SQL version of the database
  • Fixes almost all types of SQL corruption issues
  • Recovers all type of SQL data—tables, triggers, views, stored procedures, constraints, indexes, rules, and user defined functions/data types
  • Works even with large SQL databases
  • Recovers data even when the SQL Server is live
  • Supports MS SQL 2000, 2005 and 2008, 2008R2 and 2012 versions
Kernel for SQL Database - versions available
  • Demo version
    The demo version, meant educate users on SQL recovery process, is completely free. But it is not equipped with the saving facility.
  • Licensed version
    Licensed version is the fully featured version of the software and is available on purchase only. It can save all the recovered data.
How to use Kernel for SQL Database to repair corrupt MDF files?
The hallmark of Kernel for SQL Database is the simplicity of its operation. The steps are simple, the interface is intuitive, and recovery is quick. The major steps of the SQL database recovery process are outlined here:
  1. Selecting MDF file for recovery
    The Open button (in the main page of the software) opens the Select SQL Database dialogue box. In this dialogue box, one can browse for the corrupt database. If users are sure about the SQL server version, the can select the version. Otherwise, they can use the auto detect option. Finally, the recovery process can be initiated by clicking the Recover button.
  2. Previewing the recovered data
    Once the software displays the recovered objects, the users can click an object on the left tree to preview its data.
  3. Saving the recovered data
    Two saving options are available—saving to SQL Server and saving to batch file. The first option allows to save directly to an SQL Server either through server authentication or through Windows authentication. In the Batch File option, the data is stored in script files in the desired location (which can be copied to a new database).
Benefits and drawbacks of Kernel for SQL Database
The important benefits of Kernel for SQL Database are:
  • Friendly interface
    The software interface itself provides information on how to execute the recovery process. So even non-technical users can perform recovery without any help. 
  • Preview facility
    Everything recovered can be viewed on the software’s preview pane before saving. Previewing helps users to know about the recoverability of the data.
  • Automatic detection of SQL Version
    If the users know the version of the SQL, they can select it. But if they do not know, the software can automatically detect the SQL version.
  • Multiple saving options
    The software saves the recovered MDF file files to SQL Server directly. In addition, it can save files in batch file, which users can copy to a new database when required.

The major drawback of Kernel for SQL Database is:
  • No saving option in the trial version
    The trial version of Kernel for SQL Database cannot save the data. With this version, users can only preview the data. 

Final verdict
Kernel for SQL Database is a great tool for SQL recovery. As we understand, users are happy with the product. Still the product can be improved in some areas. Also, it is good if Lepide can add some limited saving facility to the trial version. 

Performance rating
Overall rating for the software is 4/5

About Andre Williams
Andre Williams, with more than three year's experience in SQL related technologies, contributes articles, blogs, and how-to tips regularly.

Social Media Profiles