SQL: sp_validatelogins then drop orphaned by single query
0
I'm quite new to t-sql and I wrote this query to identify and then drop logins that no longer exist in active directory. I have to say that somehow it works and I get the result but I feel that there is a way to do better. Is there anyone who can put me in the right direction?
Thank you!
-- delete temp tables if exist
IF OBJECT_ID('tempdb..#TMP_SP_VALIDATELOGINS') IS NOT NULL BEGIN
DROP TABLE #TMP_SP_VALIDATELOGINS
END
IF OBJECT_ID('tempdb..#mytemp') IS NOT NULL BEGIN
DROP TABLE #mytemp
END
-- find invalid logins and put them into temp table
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- add column for rowcount
set rowcount 0
select NULL mykey, * into #mytemp from #TMP_SP_VALIDATELOGINS
set rowcount 1
update #mytemp set mykey = 1
DECLARE @login NVARCHAR(MAX)
while @@rowcount > 0
begin
set rowcount 0
-- select name to drop
set @login = (select COL_NT_Login from #mytemp where mykey = 1)
declare @drop varchar(200)
set @drop = (select 'drop login [' + name + '];'
from sys.server_principals
WHERE name = @login )
exec (@drop)
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0
DROP TABLE #mytemp
DROP TABLE #TMP_SP_VALIDATELOGINS
GO
sql
sql-server tsql asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
add a comment |
0
I'm quite new to t-sql and I wrote this query to identify and then drop logins that no longer exist in active directory. I have to say that somehow it works and I get the result but I feel that there is a way to do better. Is there anyone who can put me in the right direction?
Thank you!
-- delete temp tables if exist
IF OBJECT_ID('tempdb..#TMP_SP_VALIDATELOGINS') IS NOT NULL BEGIN
DROP TABLE #TMP_SP_VALIDATELOGINS
END
IF OBJECT_ID('tempdb..#mytemp') IS NOT NULL BEGIN
DROP TABLE #mytemp
END
-- find invalid logins and put them into temp table
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- add column for rowcount
set rowcount 0
select NULL mykey, * into #mytemp from #TMP_SP_VALIDATELOGINS
set rowcount 1
update #mytemp set mykey = 1
DECLARE @login NVARCHAR(MAX)
while @@rowcount > 0
begin
set rowcount 0
-- select name to drop
set @login = (select COL_NT_Login from #mytemp where mykey = 1)
declare @drop varchar(200)
set @drop = (select 'drop login [' + name + '];'
from sys.server_principals
WHERE name = @login )
exec (@drop)
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0
DROP TABLE #mytemp
DROP TABLE #TMP_SP_VALIDATELOGINS
GO
sql
sql-server tsql asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
betterin what ways ?
– Squirrel
Nov 15 '18 at 9:35
@Squirrel ... better ... with more elegant code, maybe without create temp tables
– Maurip00
Nov 15 '18 at 9:43
If you usesp_helptextto get the definition, you'll see that all this sproc does is grab the results ofselect 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins where isntname = 1 and get_sid(loginname) is null. This is easily incorporated directly into a query (if you replace the internalget_sidfunction with the publicsuser_sidfunction).
– Jeroen Mostert
Nov 15 '18 at 10:13
add a comment |
0
0
0
I'm quite new to t-sql and I wrote this query to identify and then drop logins that no longer exist in active directory. I have to say that somehow it works and I get the result but I feel that there is a way to do better. Is there anyone who can put me in the right direction?
Thank you!
-- delete temp tables if exist
IF OBJECT_ID('tempdb..#TMP_SP_VALIDATELOGINS') IS NOT NULL BEGIN
DROP TABLE #TMP_SP_VALIDATELOGINS
END
IF OBJECT_ID('tempdb..#mytemp') IS NOT NULL BEGIN
DROP TABLE #mytemp
END
-- find invalid logins and put them into temp table
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- add column for rowcount
set rowcount 0
select NULL mykey, * into #mytemp from #TMP_SP_VALIDATELOGINS
set rowcount 1
update #mytemp set mykey = 1
DECLARE @login NVARCHAR(MAX)
while @@rowcount > 0
begin
set rowcount 0
-- select name to drop
set @login = (select COL_NT_Login from #mytemp where mykey = 1)
declare @drop varchar(200)
set @drop = (select 'drop login [' + name + '];'
from sys.server_principals
WHERE name = @login )
exec (@drop)
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0
DROP TABLE #mytemp
DROP TABLE #TMP_SP_VALIDATELOGINS
GO
sql
sql-server tsql asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
I'm quite new to t-sql and I wrote this query to identify and then drop logins that no longer exist in active directory. I have to say that somehow it works and I get the result but I feel that there is a way to do better. Is there anyone who can put me in the right direction?
Thank you!
-- delete temp tables if exist
IF OBJECT_ID('tempdb..#TMP_SP_VALIDATELOGINS') IS NOT NULL BEGIN
DROP TABLE #TMP_SP_VALIDATELOGINS
END
IF OBJECT_ID('tempdb..#mytemp') IS NOT NULL BEGIN
DROP TABLE #mytemp
END
-- find invalid logins and put them into temp table
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- add column for rowcount
set rowcount 0
select NULL mykey, * into #mytemp from #TMP_SP_VALIDATELOGINS
set rowcount 1
update #mytemp set mykey = 1
DECLARE @login NVARCHAR(MAX)
while @@rowcount > 0
begin
set rowcount 0
-- select name to drop
set @login = (select COL_NT_Login from #mytemp where mykey = 1)
declare @drop varchar(200)
set @drop = (select 'drop login [' + name + '];'
from sys.server_principals
WHERE name = @login )
exec (@drop)
delete #mytemp where mykey = 1
set rowcount 1
update #mytemp set mykey = 1
end
set rowcount 0
DROP TABLE #mytemp
DROP TABLE #TMP_SP_VALIDATELOGINS
GO
sql
sql-server tsql sql
sql-server tsql asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
edited Nov 15 '18 at 9:37
Maurip00
asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
asked Nov 15 '18 at 9:32
Maurip00Maurip00
133
133
betterin what ways ?
– Squirrel
Nov 15 '18 at 9:35
@Squirrel ... better ... with more elegant code, maybe without create temp tables
– Maurip00
Nov 15 '18 at 9:43
If you usesp_helptextto get the definition, you'll see that all this sproc does is grab the results ofselect 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins where isntname = 1 and get_sid(loginname) is null. This is easily incorporated directly into a query (if you replace the internalget_sidfunction with the publicsuser_sidfunction).
– Jeroen Mostert
Nov 15 '18 at 10:13
add a comment |
betterin what ways ?
– Squirrel
Nov 15 '18 at 9:35
@Squirrel ... better ... with more elegant code, maybe without create temp tables
– Maurip00
Nov 15 '18 at 9:43
If you usesp_helptextto get the definition, you'll see that all this sproc does is grab the results ofselect 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins where isntname = 1 and get_sid(loginname) is null. This is easily incorporated directly into a query (if you replace the internalget_sidfunction with the publicsuser_sidfunction).
– Jeroen Mostert
Nov 15 '18 at 10:13
better in what ways ?– Squirrel
Nov 15 '18 at 9:35
better in what ways ?– Squirrel
Nov 15 '18 at 9:35
@Squirrel ... better ... with more elegant code, maybe without create temp tables
– Maurip00
Nov 15 '18 at 9:43
@Squirrel ... better ... with more elegant code, maybe without create temp tables
– Maurip00
Nov 15 '18 at 9:43
If you use
sp_helptext to get the definition, you'll see that all this sproc does is grab the results of select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins where isntname = 1 and get_sid(loginname) is null. This is easily incorporated directly into a query (if you replace the internal get_sid function with the public suser_sid function).– Jeroen Mostert
Nov 15 '18 at 10:13
If you use
sp_helptext to get the definition, you'll see that all this sproc does is grab the results of select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins where isntname = 1 and get_sid(loginname) is null. This is easily incorporated directly into a query (if you replace the internal get_sid function with the public suser_sid function).– Jeroen Mostert
Nov 15 '18 at 10:13
add a comment |
1 Answer
1
active
oldest
votes
0
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- the trick is to concatenate the `DROP LOGIN` command into one long string and execute it
declare @sql nvarchar(max)
select @sql = isnull(@sql, '')
+ 'DROP LOGIN ' + quotename(p.name) + ';' + char(13)
from #TMP_SP_VALIDATELOGINS l
inner join sys.server_principals p on l.COL_NT_Login = p.name
-- Print out to verify
print @sql
-- unmask to execute
-- exec sp_executesql @sql
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53316293%2fsql-sp-validatelogins-then-drop-orphaned-by-single-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- the trick is to concatenate the `DROP LOGIN` command into one long string and execute it
declare @sql nvarchar(max)
select @sql = isnull(@sql, '')
+ 'DROP LOGIN ' + quotename(p.name) + ';' + char(13)
from #TMP_SP_VALIDATELOGINS l
inner join sys.server_principals p on l.COL_NT_Login = p.name
-- Print out to verify
print @sql
-- unmask to execute
-- exec sp_executesql @sql
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
add a comment |
0
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- the trick is to concatenate the `DROP LOGIN` command into one long string and execute it
declare @sql nvarchar(max)
select @sql = isnull(@sql, '')
+ 'DROP LOGIN ' + quotename(p.name) + ';' + char(13)
from #TMP_SP_VALIDATELOGINS l
inner join sys.server_principals p on l.COL_NT_Login = p.name
-- Print out to verify
print @sql
-- unmask to execute
-- exec sp_executesql @sql
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
add a comment |
0
0
0
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- the trick is to concatenate the `DROP LOGIN` command into one long string and execute it
declare @sql nvarchar(max)
select @sql = isnull(@sql, '')
+ 'DROP LOGIN ' + quotename(p.name) + ';' + char(13)
from #TMP_SP_VALIDATELOGINS l
inner join sys.server_principals p on l.COL_NT_Login = p.name
-- Print out to verify
print @sql
-- unmask to execute
-- exec sp_executesql @sql
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
CREATE TABLE #TMP_SP_VALIDATELOGINS
(
COL_SID varbinary(85) NOT NULL
, COL_NT_Login SYSNAME NOT NULL
)
INSERT INTO #TMP_SP_VALIDATELOGINS
EXEC sp_validatelogins
-- the trick is to concatenate the `DROP LOGIN` command into one long string and execute it
declare @sql nvarchar(max)
select @sql = isnull(@sql, '')
+ 'DROP LOGIN ' + quotename(p.name) + ';' + char(13)
from #TMP_SP_VALIDATELOGINS l
inner join sys.server_principals p on l.COL_NT_Login = p.name
-- Print out to verify
print @sql
-- unmask to execute
-- exec sp_executesql @sql
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
answered Nov 15 '18 at 9:51
SquirrelSquirrel
11.9k22127
11.9k22127
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53316293%2fsql-sp-validatelogins-then-drop-orphaned-by-single-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
betterin what ways ?– Squirrel
Nov 15 '18 at 9:35
@Squirrel ... better ... with more elegant code, maybe without create temp tables
– Maurip00
Nov 15 '18 at 9:43
If you use
sp_helptextto get the definition, you'll see that all this sproc does is grab the results ofselect 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins where isntname = 1 and get_sid(loginname) is null. This is easily incorporated directly into a query (if you replace the internalget_sidfunction with the publicsuser_sidfunction).– Jeroen Mostert
Nov 15 '18 at 10:13