Query to get details of permissions on Database objects

Use below query to get details of permissions on Database objects

SELECT ulogin.name AS [User Name],
CASE princ.type WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' WHEN 'S' THEN 'SQL User' END AS [User Type],
princ.name AS [Database User Name], perm.permission_name AS [Permission Type], perm.state_desc AS [Permission State],
CASE perm.class WHEN 1 THEN obj.type_desc ELSE perm.[class_desc] END AS [Object Type],
CASE perm.class WHEN 1 THEN OBJECT_NAME(perm.major_id) WHEN 3 THEN schem.[name] WHEN 4 THEN imp.[name] END AS [Object Name],
col.name AS [Column Name]
FROM sys.database_principals princ LEFT JOIN sys.server_principals ulogin on princ.sid = ulogin.sid LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id  LEFT JOIN sys.database_principals imp ON imp.principal_id = perm.major_id LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id  LEFT JOIN sys.schemas schem ON schem.schema_id = perm.major_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE princ.[type] IN ('S','U','G') AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

Log backup to replace maintenance plan

Use below script to restore maintenance plan from log backup:

USE [Master]
GO
DECLARE @BackupExtention nVarChar(4); SET @BackupExtention = '.trn'
DECLARE @DB nVarChar(128); SET @DB = ''
DECLARE @BackupDir nVarChar(138);

--loop through each databASe in full recovery mode
WHILE @DB < ( SELECT MAX(Name) FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' )
BEGIN
SELECT @DB = MIN (Name)  FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' AND Name > @DB
DECLARE @DatabaseName nVarChar(128); SET @DatabaseName = @DB --SET @DatabaseName = 'TFSWarehouse'
DECLARE @DateTimestamp nVarChar(14); SET @DateTimestamp = '_' + CONVERT(VarChar, GetDate(), 112) + '_' + replace(CONVERT(VarChar, GetDate(), 108),':','')
DECLARE @RemoteBackupPath nVarChar(260);
SET @BackupDir = N'D:\Backup\' + @DatabaseName
SET @RemoteBackupPath = @BackupDir + N'\' + @DatabaseName + @DateTimestamp + @BackupExtention
DECLARE @BackupDevice nVarChar(128); SET @BackupDevice = N'local_' + @DatabaseName + @BackupExtention
--SELECT @DatabaseName, @BackupDevice, @RemoteBackupPath
--drop AND recreate the backup device
IF Exists(SELECT NULL FROM sys.Backup_Devices WHERE Name = @BackupDevice)
EXEC Master..sp_DropDevice @LogicalName = @BackupDevice
EXEC Master.dbo.sp_AddumpDevice @DevType = N'disk', @LogicalName = @BackupDevice, @PhysicalName = @RemoteBackupPath
--create the subdirectory if not already preset
EXECUTE Master.dbo.xp_Create_SubDir @BackupDir
--execute the backup
BACKUP LOG @DatabaseName TO  DISK = @RemoteBackupPath WITH NOFORMAT, NOINIT,  Name = @BackupDevice, SKIP, REWIND, NOUNLOAD,  STATS = 10
END
GO

Unable to open DTS package in SQL 2008 Management Studio

Sometime when we try to import a SQL Server 2000 DTS package into SQL Server 2008 R2 using SSMS, the following error appears:

Managing DTS packages in SQL Server Management Studio requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, on your SQL Server 2008 R2 installation media, locate and run the Windows Installer package for SQL Server 2005 Backward Compatibility (SQLServer2005_BC*.msi). You can also download the DTS runtime from the SQL Server Feature Pack page on the Microsoft Download Center. (Microsoft.SqlServer.DtsObjectExplorerUI)

As suggested, the “Microsoft SQL Server 2005 Backward compatibility” package must be installed. It can be found on this location: http://www.microsoft.com/en-us/download/details.aspx?id=3522

After the installation of “Microsoft SQL Server 2005 Backward compatibility” package the DTS package is imported successfully. Now when we try to open the DTS package, the following error appears:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

To solve this error the “Sql Server 2000 DTS Designer Components” package must be installed. It can be found on this location: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11988

After the installation try to open again the DTS package in SSMS.   

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

If the same error appears, copy the DLL and RLL files as described in below Microsoft link: http://msdn.microsoft.com/en-us/library/ms143755.aspx

To ensure that the DTS designer can be used in SQL Server Management Studio

1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.

2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id% folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder.

For example, for U.S. English, the lang_id subfolder will be "1033".
To ensure that the DTS designer can be used in Business Intelligence Development Studio

1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder.

2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be "1033".

List Table with Identity Column

You can use below queries to get the list of all the tables with Identity column:

SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]
FROM syscolumns WHERE COLUMNPROPERTY(ID, Name, 'IsIdentity') = 1

Or use can use below query also

SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]
FROM syscolumns WHERE STATUS = 0x80

You can use blow query to check whether the table has Identity column:

SELECT Name AS [Table], OBJECTPROPERTY(ID, 'TableHasIdentity') AS [Has_Identity]
FROM SysObjects WHERE xType = 'U'

Cannot alter the table 'Table_Name' because it is being published for replication.

Today I was altering Primary key columns datatype in a table and got below error in Transaction replication.

Msg 4929, Level 16, State 1, Line 1
Cannot alter the table 'Table_Name' because it is being published for replication.

I had taken following steps to solve the problem

• Remove table from the replication.
• Change the datatype of Primary key column.
• Add again add table to the replication and reinitialize the publication.