블로그 이미지
010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (517)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total
Today
Yesterday

달력

« » 2025.4
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30

공지사항

최근에 올라온 글

https://sqlgeekspro.com/server-principal-owns-one-or-more-server-roles/

 

Error 15141: The Server Principal Owns One or More Server Roles » SQLGeeksPro

This article describes the error 15141: The Server Principal Owns One or More Server Roles and provides necessary scripts to resolve the error message.

sqlgeekspro.com

 

 

Today we are going to discuss about how to resolve the “Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped”

Introduction

If you are a SQL Server DBA, you may encounter the Error 15141 when trying to delete a login id. First of all let me show you how the error message looks like in SSMS when you try to delete/drop a login.

In the above example screen shot we are trying to delete the login id ‘Admin’. I have observed that some DBA(s) do not read the error message carefully and starts beating about the bush.

There are very similar error messages while dropping logins as you can refer the following links:

Error 15141: The Server Principal Owns One or More Availability Groups

Error 15141: The Server Principal Owns One or More Endpoints

Error 15434: Could not drop login as the user is currently logged in

To emphasize if you observe the above error message clearly reads that the principal (or login) owns one or more server roles, which prevents you from dropping it.

Cause Of the Error 15141: The Server Principal Owns One or More Server Roles

When a server principal or login owns a server role, you cannot drop the login unless you first transfer ownership of the server role to another login. As a matter of fact SQL Server never allows you to drop a login if it owns any object. Hence it throws the error preventing you to drop the server principal or login.

Resolution

To resolve the error, you first need to identify the Server Roles owned by the login or server principal. Then you need to transfer the ownership for each of the server roles to ‘sa’ or any other login as per your organization standard.

1. Query to Identify the Server Roles the Login owns

SELECT sp1.name AS ServerRoleName, 
       sp2.name AS RoleOwnerName
       FROM sys.server_principals AS sp1
       JOIN sys.server_principals As sp2
       ON sp1.owning_principal_id=sp2.principal_id
       WHERE sp2.name='Admin' --Change the login name

Sample Output:

Here in the above example it shows that the login id ‘Admin’ owns two Server roles. On the contrary if the login would have own one or more database role(s), it would allow to delete the login but not the user. Now we’ll change the ownership.

2. Query to Change the Server Role Owner:

USE [master]
GO
ALTER AUTHORIZATION ON SERVER ROLE :: [ServerRole-Test] TO [sa] --Change The ServerRole Name and login Name
GO
ALTER AUTHORIZATION ON SERVER ROLE :: [AnotherServerRole-Test] TO [sa] --Change The ServerRole Name and login Name
GO

3. Drop the Login:

USE [master]
GO
DROP Login [Admin] --change the login Name

Query Result:

As can be seen now the the drop command completed successfully.

Conclusion:

To summarize the error Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped occurs when the login or server principal owns one or more Server Roles and you are trying to drop the login. Obviously you should not take any knee jerk reaction. After all you are trying to resolve the issue. Hence you should first list out the Server Roles which the login owns. Then change the ownership to [sa] or any other login as per your organization standards. Finally delete or drop the login. Definitely this should resolve the issue. Important to realize that this method will allow you to delete the login even if the login owns and database roles. Hence to repeat you need to be very careful while working in production systems.

Posted by 보미아빠
, |

subnet 나누기

카테고리 없음 / 2025. 4. 24. 16:44

* nCloud.com Classic Path 서비스에서 서브넷 구성 

10.36.192.0/19가 지정되어 있으므로, 2^(32-19) = 8192개 IP를 할당할 수 있는 VPC를 구성할 수 있다. 

멀티존 구성을 위해 서브넷을 나누어야하고, ncloud는 KR-1, KR-2 2개의 존이 있으므로 할당받은 ip를 2개의 서브넷으로 나누면
각각 4096개 IP를 수용할 수 있는 2개의 서브넷을 구성할 수 있다. 
2^(32-20) = 4096개 이므로 /20 CIDR(Classless Inter-Domain Routing)이 된다. 

첫 번째 서브넷 1의 시작값은 10.36.192.0/20 이며, 
두 번째 서브넷 2의 시작값은 10.36.208.0/20 이다. 

두 번째 서브넷 2의 시작값 계산은 아래와 같이 할 수 있다. 
4096 (서브넷의 가용 IP) / 256 (8bit) = 16 증가 
3번째 옥텟값 192에서 16을 더하면 208이므로, 두 번째 서브넷 2는 10.36.208.0/20이 된다. 

위 값으로 2개의 서브넷을 구성한다. 
https://www.site24x7.com/ko/tools/ipv4-subnetcalculator.html 사이트를 참고하면 더 쉽게 서브넷을 계산할 수 있다.

Posted by 보미아빠
, |

백업 튜닝

카테고리 없음 / 2025. 3. 12. 11:57

단계별 성능 테트스 후 최적값을 찾는다.

 

항목

---------------------------------------------------------

compression,

maxtransfersize (64K 단위로 4MB 까지 커질수 있고 1MB 가 default 임),

buffercount (계산값이 default))

 

백업 후 옵션 값 확인 

---------------------------------------------------------

dbcc traceon (3605,-1)
dbcc traceon (3213,-1)

 

 

 

단 영향도는 CPU 를 더 많이 쓰고, Disk I/O 도 증가할 수 있으므로 적절하게 튜닝해 운영 쿼리에 영향이 가지 않도록 한다. 약 7시간 걸리던 백업이 2시간으로 줄었다. (수십 TB)

 

 

튜닝 결과 

---------------------------------------------------------

 

Posted by 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함