SQL Server Error: The database principal owns a schema in the database, and cannot be dropped.

Unable to delete SQL Server user from database, error: The database principal owns a schema in the database, and cannot be dropped.

Workaround:

Example: to delete user “testadmin” from database “TestDB”.

USE TestDB;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('testadmin');

-- outcome e.g. db_denydatareader

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;

Advertisements

What’s new in SQL Server 2014

image

Reference: http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

SQL Server 2012 – New T-SQL Function

–Parse String (amount with currency) to money
SELECT PARSE(‘€791,33’ AS money USING ‘de-DE’)

— Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
SELECT  TRY_CONVERT(integer, ‘hello’) 
SELECT TRY_PARSE(‘Jabberwokkie’ AS datetime2 USING ‘en-US’) AS Result

— Return Orange, choose 2nd item from the list
SELECT CHOOSE ( 2, ‘Apple’, ‘Orange’, ‘Lemon’ ) AS Result;

— Return Biro, similar to excel function
SELECT IIF ( 7 > 9, ‘Amar’, ‘Biro’ ) AS Result;

— Return “Jack Monde 123”, Combine 2 or more object, accept string, null, number, etc
SELECT CONCAT ( ‘Jack  ‘, ‘Monde ‘, NULL, 123 )

— Date Formatting Function
DECLARE @d DATETIME = ’10/16/2005′;

SELECT FORMAT ( @d, ‘d’, ‘de-de’ ) AS ‘German’
SELECT FORMAT ( @d, ‘d’, ‘en-gb’ ) AS ‘Great Britain’
SELECT FORMAT ( @d, ‘d’, ‘zh-cn’ ) AS ‘Simplified Chinese’

— Return:
image

— New Dynamic Management Views & Functions

SELECT * FROM  sys.dm_os_volume_stats (1, 1)

SELECT * FROM  sys.dm_server_services

SELECT * FROM  sys.dm_server_registry 

SQL Server 2012 – How to enable contained database authentication

— Enable contained database authentication

sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE
GO

— Create Database with User & Password stored in the database itself.

CREATE DATABASE [MyConAuthDB] CONTAINMENT=PARTIAL
GO

USE [MyConAuthDB]
GO

CREATE TABLE baseTable(id int, [name] varchar (255))
GO

CREATE USER testuser1 WITH PASSWORD = N’password’, DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember’db_owner’, ‘testuser1’
GO

SQL Server 2012 – How to create Read Only Admin Role

USE [master]
GO

CREATE SERVER ROLE [ReadOnly_Admin]
GO

GRANT VIEW ANY DATABASE TO [ReadOnly_Admin]
GO

GRANT VIEW SERVER STATE  TO [ReadOnly_Admin]
GO

GRANT VIEW ANY DEFINITION TO [ReadOnly_Admin]
GO

Create HTML/XML Emails Using SQL Server

Step 1: Configure Your Database Mail
Step 2: Use the following stored procedure

    exec  msdb.dbo.sp_send_dbmail
    @profile_name =@profile,
    @recipients = @To,
    @copy_recipients = @Cc,
    @blind_copy_recipients = @Bcc,
    @subject =@Subject,
    @body =@Body,
    @body_format=@Format,
    @file_attachments=@Attachment,
    @exclude_query_output=1

Step 3: Create your SQL Query 

declare @BodyContent varchar(max),
      @TableHeader varchar(max),
      @TableFooter varchar(max)

Set @TableFooter = ‘</table></body></html>’;
Set @TableHeader = ‘<html><head>’ +
                  ‘<style>’ +
                  ‘td {padding-left:5px;padding-right:5px;padding-top:5px;padding-bottom:5px;font-family: Arial, Helvetica, sans-serif; font-size: 10pt;} ‘ +
                  ‘</style>’ +
                  ‘</head>’ +
                  ‘<body><table cellpadding="5" cellspacing="0" border="1" bordercolor="#C0C0C0" style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; border-collapse:collapse;border-bottom-style:solid;">’ +
                  ‘<tr bgcolor=#FFEFD8><td align=center style="background-color: #359AFF; color: white"><b>Server Name</b></td>’ +
                  ‘<td align=center style="background-color: #359AFF; color: white"><b>Product</b></td>’ +
                  ‘<td align=center style="background-color: #359AFF; color: white"><b>Data Source</b></td>’;
Select @BodyContent = (Select Row_Number() Over(Order By name) % 2 As [ALTCOLOR],
            name As [TD],
            product As [TD],
            data_source As [TD align=center]
      From sys.servers
      Order By name
      For XML raw(‘tr’), Elements)

Set @BodyContent = Replace(@BodyContent, ‘_x0020_’, space(1))
Set @BodyContent = Replace(@BodyContent, ‘_x003D_’, ‘=’)
Set @BodyContent = Replace(@BodyContent, ‘<tr><ALTCOLOR>1</ALTCOLOR>’, ‘<tr bgcolor=#DFFFDF>’)
Set @BodyContent = Replace(@BodyContent, ‘<ALTCOLOR>0</ALTCOLOR>’, ”)

Select @BodyContent = @TableHeader + @BodyContent + @TableFooter

Select @BodyContent

Step 4: Use msdb.dbo.sp_send_dbmail to send email @body=@BodyContent

@BodyContent Output:
image

To determine working hour using T-SQL

create function [dbo].[fn_IsWorkingHour]
(
@Datetime datetime
)
RETURNS bit
AS
BEGIN
declare @result bit
declare @currTime datetime
set @currTime = (@Datetime – CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime))
set @result = 0
if datediff(minute,’1900-01-01′,@currTime) >= 540 and datediff(minute,’1900-01-01′,@currTime) <= 1050
  set @result = 1
else
  set @result = 0
return @result
END

Note: Highlighted in red (working hour 9 AM to 5.30 PM)
540 = 9:00 AM  = (9 * 60)
1050 = 5:30 PM  = (17.5 * 60)