Mixing
SQL Server - Remove all logins that they are asossiated with orphan users
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
I have an SQL Server 2008R2 that had lots of databases.
The procedure was that a logins were created and users were mapped to databases.
Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.
Is there a batch way to get rid of the these logins and their assosiated users?
Thank you
sql-server sql-server-2008 delete logins
asked 3 hours ago
PanosPlat
2301410
add a comment |Â
up vote
2
down vote
favorite
I have an SQL Server 2008R2 that had lots of databases.
The procedure was that a logins were created and users were mapped to databases.
Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.
Is there a batch way to get rid of the these logins and their assosiated users?
Thank you
sql-server sql-server-2008 delete logins
asked 3 hours ago
PanosPlat
2301410
1
You can look at blog.dbi-services.com/…
– Spörri
1 hour ago
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I have an SQL Server 2008R2 that had lots of databases.
The procedure was that a logins were created and users were mapped to databases.
Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.
Is there a batch way to get rid of the these logins and their assosiated users?
Thank you
sql-server sql-server-2008 delete logins
asked 3 hours ago
PanosPlat
2301410
I have an SQL Server 2008R2 that had lots of databases.
The procedure was that a logins were created and users were mapped to databases.
Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.
Is there a batch way to get rid of the these logins and their assosiated users?
Thank you
sql-server sql-server-2008 delete logins
sql-server sql-server-2008 delete logins
asked 3 hours ago
PanosPlat
2301410
asked 3 hours ago
PanosPlat
2301410
edited 2 hours ago
asked 3 hours ago
PanosPlat
2301410
asked 3 hours ago
PanosPlat
2301410
asked 3 hours ago
PanosPlat
2301410
2301410
1
You can look at blog.dbi-services.com/…
– Spörri
1 hour ago
add a comment |Â
1
You can look at blog.dbi-services.com/…
– Spörri
1 hour ago
1
1
You can look at blog.dbi-services.com/…
– Spörri
1 hour ago
You can look at blog.dbi-services.com/…
– Spörri
1 hour ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.
If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.
To find these logins with no database mapping please look here!
However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).
answered 1 hour ago
Bahtiyar Samet Çoban
1054
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.
If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.
To find these logins with no database mapping please look here!
However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).
answered 1 hour ago
Bahtiyar Samet Çoban
1054
add a comment |Â
up vote
2
down vote
accepted
First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.
If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.
To find these logins with no database mapping please look here!
However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).
answered 1 hour ago
Bahtiyar Samet Çoban
1054
add a comment |Â
up vote
2
down vote
accepted
up vote
2
down vote
accepted
First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.
If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.
To find these logins with no database mapping please look here!
However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).
answered 1 hour ago
Bahtiyar Samet Çoban
1054
First of all, Orphaned Users is a term to define database users associated to non-existing logins. According to your explanation, that is not the case for you and your logins.
If databases have been removed from the server, there will be no orphaned user since they will be gone with the database, there will be logins which have no associated users and probably not in use.
To find these logins with no database mapping please look here!
However, To get rid of these logins with no mapping you need to be sure that there is no activity, you can try several methods such as Extended Events, Profiler, Server Audit etc to capture all login activity, parse login events and detect unused logins.
Extended events would be the best one since it is faster and requires less resource. I have encountered the same problem and developed a solution for this years ago with Extended Events. The script creates an SQL Agent job which creates everything necessary dynamically.An extended events to capture all login events on server writes events to a file on C drive which is then parsed by the job and logs everything into a summary table.
I can upload the script if you want but unfortunately there is no documentation or blog post about it (yet).
answered 1 hour ago
Bahtiyar Samet Çoban
1054
answered 1 hour ago
Bahtiyar Samet Çoban
1054
answered 1 hour ago
Bahtiyar Samet Çoban
1054
answered 1 hour ago
Bahtiyar Samet Çoban
1054
1054
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221617%2fsql-server-remove-all-logins-that-they-are-asossiated-with-orphan-users%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
You can look at blog.dbi-services.com/…
– Spörri
1 hour ago