I am not able to drop a SQL user and keep getting this error message;
Msg 15138, Level 16, State 1, Line 5
The database principal owns a schema in the database, and cannot be dropped.
There is an orphan user who owns a schema or role and can not be dropped until user is detached from schema/role.
First see if there is any role associated and remove it;
-- Query to get the orphan users
EXEC sys.sp_change_users_login 'REPORT'
-- Query to get the user associated Database Role
SELECT
DBPrincipal_2.name as [Role], DBPrincipal_1.name as [OWNER]
FROM sys.database_principals as DBPrincipal_1
INNER JOIN sys.database_principals as DBPrincipal_2
ON DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id
WHERE DBPrincipal_1.name = 'ADDUSER'
--Query to fix the role
ALTER AUTHORIZATION ON ROLE::[db_owner] TO [dbo]
SSMS STEPS: Object Explorer->Target Server->Target Database->Security->roles->Right click on database role. Change user name to your selected name or “dbo” and click OK.
Now fix the issue where we will transfer the ownership of the database role/schema to dbo.
----*** Query to get the user associated schema
select * from information_schema.schemata
where schema_owner = MyUser'
--Query to fix the error Msg 15138 on database schema
ALTER AUTHORIZATION ON SCHEMA::[MyDatabaseSchema] TO [dbo]
--Query to drop the user
DROP USER [MyUser]
GO
SSMS STEPS: Object Explorer->Target Server->Target Database -> Security->Schemas->Right Click on schema->Change user name to your selected name or “dbo”.
Schema and/or database role has been transferred to “dbo”. You are safe to drop the user.
We can use Regular Expression to find and replace, valid with all versions of SSMS
Find what: {.+}
Replace with: ‘\1’,
Look in: Selection
Expand Find Option
Use: Regular expression (checked)
That regular expression indicates find everything and remember what we found Replace everything we found \1 by wrapping it with tic marks and a comma.
If you have more complex requirements, the right chevron next to the drop down arrow on Find what lists the regular expression dialect SSMS/Visual Studio understands
SQL Server 2012 has two new analytical functions, LEAD() AND LAG(). These functions return data from Next row (LEAD) and Previous row (LAG) of the same dataset without using self-join.
Let’s go with an example;
This is my initial dataset;
Here is the query to create this dataset;
IF OBJECT_ID('tempdb..#SampleDataSet') is not null drop table #SampleDataSet
SELECT *
INTO #SampleDataSet
FROM
(
SELECT 1 LineId, '2017-04-01' StockId, 'Toyota' AS Make, 'Brown' AS Color, 'Sale' AS ActionType, '2017-4-15' ActionDate UNION
SELECT 2 LineId, '2017-04-01' StockId, 'Toyota' AS Make, 'Brown' AS Color, 'Quote' AS ActionType, '2017-4-10' ActionDate UNION
SELECT 3 LineId, '2017-05-01' StockId,'Toyota' AS Make, 'Red' AS Color, 'Sale' AS ActionType, '2017-5-15' ActionDate UNION
SELECT 4 LineId, '2017-05-01' StockId, 'Toyota' AS Make, 'Red' AS Color, NULL AS ActionType, '2017-5-10' ActionDate UNION
SELECT 5 LineId, '2017-06-01' StockId, 'Toyota' AS Make, 'Pink' AS Color, 'Sale' AS ActionType, '2017-6-15' ActionDate UNION
SELECT 6 LineId, '2017-06-01' StockId, 'Toyota' AS Make, 'Pink' AS Color, 'Quote' AS ActionType, '2017-6-10' ActionDate UNION
SELECT 7 LineId, '2017-07-01' StockId, 'Toyota' AS Make, 'Green' AS Color, 'Sale' AS ActionType, '2017-7-15' ActionDate UNION
SELECT 8 LineId, '2017-07-01' StockId, 'Toyota' AS Make, 'Green' AS Color, 'Quote' AS ActionType, '2017-7-10' ActionDate UNION
SELECT 9 LineId, '2017-08-01' StockId, 'Honda' AS Make, 'Silver' AS Color, 'Sale' AS ActionType, '2017-8-15' ActionDate UNION
SELECT 10 LineId, '2017-08-01' StockId, 'Honda' AS Make, 'Silver' AS Color, 'Quote' AS ActionType, '2017-8-10' ActionDate UNION
SELECT 11 LineId, '2017-09-01' StockId, 'Honda' AS Make, 'Brown' AS Color, 'Sale' AS ActionType, '2017-9-15' ActionDate UNION
SELECT 12 LineId, '2017-09-01' StockId, 'Honda' AS Make, 'Brown' AS Color, NULL AS ActionType, '2017-9-10' ActionDate UNION
SELECT 13 LineId, '2017-10-01' StockId, 'Honda' AS Make, 'Red' AS Color, 'Sale' AS ActionType, '2017-10-15' ActionDate UNION
SELECT 14 LineId, '2017-10-01' StockId, 'Honda' AS Make, 'Red' AS Color, 'Quote' AS ActionType, '2017-10-10' ActionDate
) src
WHERE 1=1
/*
This is my initial dataset
*/
SELECT * FROM #SampleDataSet src
I am adding SeqCount and Seq column to identify each group of car (make and color) and each action in the group. LAG and Lead values of LineId column are used to demonstrate function output.
SELECT
src.LineId, src.StockId,
ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ActionDate DESC) Seq,
COUNT(*) OVER (PARTITION BY StockId) SeqCount,
src.Make, src.Color, src.ActionType, src.ActionDate,
LEAD(src.LineId) OVER (ORDER BY src.LineId) LeadValue,
LAG(src.LineId) OVER (ORDER BY src.LineId) LagValue
FROM #SampleDataSet src
This is the output of above query;
You can see the application of LEAD() and LAG() function in last two columns on the right side of above dataset.
One of the possible application of this function. As a business rule every quote action must be preceded by sale action in a car dealer dataset. If quote action is null in a group, fix it.
SELECT
src.StockId, src.Seq, src.SeqCount, src.Make, src.Color, src.ActionType,
CASE WHEN src.LeadValue = 'Sale' AND src.ActionType IS NULL THEN 'Quote' ELSE src.ActionType END dActionType,
src.ActionDate, src.LeadValue, src.LagValue
FROM
(
--Dataset transformation
SELECT
src.StockId,
ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY ActionDate DESC) Seq,
COUNT(*) OVER (PARTITION BY StockId) SeqCount,
src.Make, src.Color, src.ActionType, src.ActionDate,
LEAD(ActionType) OVER (ORDER BY ActionDate DESC) LeadValue,
LAG(ActionType) OVER (ORDER BY ActionDate DESC) LagValue
FROM #SampleDataSet src
) src
WHERE 1=1
Here is the output;
dActionType is a derived column that has all the correct sequence of actions with the help of Lead() and LAG() function.
Today I have received this error on a remote server connecting from my local computer;
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and the SQL server is configured to allow remote connections. (provider: Named Pipes, Provider, error:40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2).
To fix this error goto start menu–> go to Microsoft Sql Server –> go to configurations folder and click on sql server configuration manager. check below image.
Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those
Expand Server Network Configuration In Protocols for Sql Server here Enable Shared,Named,TCP/IP
Expand Sql Native client 11.0 Configuration manager. In client protocols you will see TCP/IP, named Pipes,Via disabled, enable those and restart the Sql related services. Now the error fixed.
