[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1031
  • Last Modified:

Stored procedure to delete orphaned records

Hello all. After running sp_validatelogins I'm finding quite a few orphaned records. I've looked in Active Directory and these accounts are indeed gone. Does anyone have or can tell me how to create a stored procedure to delete all orpaned records in SQL Server?  Thanks, Jordan
0
JordanKingsley
Asked:
JordanKingsley
  • 17
  • 12
  • 3
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Atlanta_MikeCommented:
If you have proper foreign key constraints you shouldn't have nay orphaned records?

Are you talking about the syslogins table having no longer valid logins?
0
 
JordanKingsleyAuthor Commented:
I have an application that takes Active Directory accounts and it adds them to the SQL Server logins in security through it's interface. Once they've been added in SQL, they remain even after they're removed from Active Directory.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
JordanKingsleyAuthor Commented:
That's a really great page aneeshattingal ! I'm going to test it.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Ok .. Make a try
0
 
JordanKingsleyAuthor Commented:
That article is great, but it pertains to sql logins, this problem is windows authentication that has been removed in AD but remains in SQL
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Take a look at this  article
http://support.microsoft.com/kb/314546

and check Step 3: How to resolve orphaned users
0
 
JordanKingsleyAuthor Commented:
Hello aneeshattingal, again the scenario is a bit different. My databases aren't being moved or restored to another server. (When I try the examples the orphaned users aren't orphaned, they're in both AD and SQL). When I run "sp_validatelogins" is when I find users with SQL accounts but no accounts in Active Directory. Against this comparrison are the orphan users I need removed with a stored procedure (I will put it in a scheduled job).
0
 
Scott PletcherSenior DBACommented:
In theory it's as simple as cursoring thru the list returned by sp_validateLogins and issuing an sp_revokeLogin on each one ... *except* that you must first remove any related user from all individual databases ... *which requires* that the user not own anything in the db.

This will take a comprehensive script which will not necessarily be simple or short, although invoking/executing it won't be that difficult :-) .  With that understanding, if you want a script, I will create one.
0
 
Atlanta_MikeCommented:
I've never seen this as an issue?

Does exec sp_change_users_login 'Report' return the users you are seeing orphaned?

0
 
JordanKingsleyAuthor Commented:
If you would Scott I'd sure owe you ! :^)
0
 
Scott PletcherSenior DBACommented:
OK, working on it -- need it at work too! -- but it may take a while.
0
 
JordanKingsleyAuthor Commented:
Thanks so much Scott !!! Take all the time in the world.
0
 
Atlanta_MikeCommented:
Thanks Scott, it'll save me from having to write it to and that'll be a good one to add to the admin scripts "vault" :-)
0
 
Scott PletcherSenior DBACommented:
OK, this seems to be working reasonably well.  This code does *not* automatically run the commands to remove the login(s), but it does generate them.

Since ownership for UDTs cannot be directly changed, if the login has a related user that owns a UDT(s), that will be reported, but you will have to address it yourself.  Same, right now, for permissions GRANTed, it just warns you but doesn't generate a command explicitly for that.

Note that I have *not* yet run the generated code, although I have parsed it and looked it over.  If you find any errors / have any concerns, please let me know.


IF OBJECT_ID('tempdb.dbo.#loginsToCheck') IS NOT NULL
    DROP TABLE #loginsToCheck
CREATE TABLE #loginsToCheck (
    sid VARBINARY(85) NOT NULL,
    ntLogin SYSNAME NOT NULL
      )
IF OBJECT_ID('tempdb.dbo.#uidObjects') IS NOT NULL
    DROP TABLE #uidObjects
CREATE TABLE #uidObjects (
    sid VARBINARY(85) NOT NULL,
    dbName VARCHAR(100),
    uid INT NOT NULL,
    userName VARCHAR(100) NOT NULL,
    reason SMALLINT NOT NULL,
    description VARCHAR(40) NOT NULL,
    name VARCHAR(100) NULL,
    type VARCHAR(40) NULL,
    grantee VARCHAR(100) NULL
      )

SET NOCOUNT ON
INSERT INTO #loginsToCheck
EXEC master.dbo.sp_validatelogins

SET NOCOUNT OFF
SELECT CAST(ntLogin AS VARCHAR(50)) AS [Invalid Logins],
    sid
FROM #loginsToCheck
ORDER BY ntLogin

DECLARE @sql1 VARCHAR(2000)
DECLARE @sql2 VARCHAR(2000)

TRUNCATE TABLE #uidObjects

SET NOCOUNT ON
SET @sql1 = '
INSERT INTO #uidObjects
SELECT lgnc.sid, ''?'', usr.uid, usr.name,
    CASE WHEN usr.uid = 1 THEN 1 ELSE 2 END,
    CASE WHEN usr.uid = 1 THEN ''Is Dbo'' ELSE ''Has Db Access'' END,
    '''', NULL, NULL
FROM #loginsToCheck lgnc
INNER JOIN [?].dbo.sysusers usr WITH (NOLOCK) ON usr.sid = lgnc.sid

/* Base logic copied from "sp_MScheck_uid_owns_anything" */
INSERT INTO #uidObjects
SELECT lgnc.sid, ''?'', usr.uid, usr.name, 4,
    ''Owns Object'', obj.name, obj.xtype, NULL
FROM #loginsToCheck lgnc
INNER JOIN [?].dbo.sysusers usr WITH (NOLOCK) ON usr.sid = lgnc.sid
    AND usr.uid <> 1
INNER JOIN [?].dbo.sysobjects obj WITH (NOLOCK) ON obj.uid = usr.uid

INSERT INTO #uidObjects
SELECT lgnc.sid, ''?'', usr.uid, usr.name, 6,
    ''Owns Type (UDT)'', typ.name, TYPE_NAME(typ.xtype), NULL
FROM #loginsToCheck lgnc
INNER JOIN [?].dbo.sysusers usr WITH (NOLOCK) ON usr.sid = lgnc.sid
    AND usr.uid <> 1
INNER JOIN [?].dbo.systypes typ WITH (NOLOCK) ON typ.uid = usr.uid
'
SET @sql2 = '
INSERT INTO #uidObjects
SELECT lgnc.sid, ''?'', usr.uid, usr.name, 8,
    ''GRANTed Permission'', grto.name, NULL, grte.name
FROM #loginsToCheck lgnc
INNER JOIN [?].dbo.sysusers usr WITH (NOLOCK) ON usr.sid = lgnc.sid
    AND usr.uid <> 1
INNER JOIN [?].dbo.syspermissions prm WITH (NOLOCK) ON
    prm.grantor = usr.uid
LEFT OUTER JOIN [?].dbo.sysobjects grto WITH (NOLOCK) ON grto.id = prm.id
LEFT OUTER JOIN [?].dbo.sysusers grte WITH (NOLOCK) ON grte.uid = prm.grantee

INSERT INTO #uidObjects
SELECT lgnc.sid, ''?'', usr.uid, usr.name, 10,
    ''Owns Role'', rol.name, CASE WHEN rol.isSqlRole = 1 THEN ''SQL''
    ELSE ''App'' END, NULL
FROM #loginsToCheck lgnc
INNER JOIN [?].dbo.sysusers usr WITH (NOLOCK) ON usr.sid = lgnc.sid
    AND usr.uid <> 1
INNER JOIN [?].dbo.sysusers rol WITH (NOLOCK) ON
    rol.altuid = usr.uid AND (rol.isSqlRole = 1 OR rol.isAppRole = 1)
'
--PRINT @sql1
--PRINT @sql2
EXEC sp_MSForEachDb @command1 = @sql1, @command2 = @sql2

SET NOCOUNT OFF
PRINT ''
PRINT '? = May need resolved by hand.'
PRINT '! = MUST be resolved by hand!'
SELECT CASE WHEN uobj.reason IN (6, 8, 10) THEN '!'
        WHEN uobj.reason IN (4) THEN '?' ELSE '' END AS [Hand?],
    CAST(lgnc.ntLogin AS VARCHAR(30)) AS [Login],
    LEFT(uobj.Description, 20) AS [Description],
    LEFT(dbName, 35) AS [Database Name],
    uobj.uid AS [UserId],
    CAST(uobj.userName AS VARCHAR(30)) AS [User Name],
    CAST(uobj.name AS VARCHAR(35)) AS [Object Name],
    uobj.Type,
    uobj.Grantee,
    uobj.SID
FROM #uidObjects uobj
INNER JOIN #loginsToCheck lgnc ON lgnc.sid = uobj.sid
ORDER BY 2, 4, 6, 7

SET NOCOUNT ON
SELECT 'EXEC [' + dbName + '].dbo.sp_changeDbOwner ''sa'''
    AS [--Change Db Owners]
FROM #uidObjects uobj
WHERE reason = 1
ORDER BY dbName

SELECT 'EXEC [' + dbName + '].dbo.sp_changeObjectOwner ''' +
    userName + '.' + name + ''', ''dbo''  --objType="' + type + '"'
    AS [--Change Object Owners]
FROM #uidObjects uobj
WHERE reason = 4
ORDER BY dbName

SELECT CASE WHEN EXISTS(
    SELECT 1
    FROM #uidObjects uobj2
    WHERE uobj2.sid = uobj.sid
    AND uobj2.dbName = uobj.dbName
    AND uobj2.uid = uobj.uid
    AND reason IN (6, 8, 10) ) THEN '--!' ELSE '' END +
    'EXEC [' + dbName + '].dbo.sp_revokeDbAccess ''' + userName + '''' +
    CASE WHEN EXISTS(
    SELECT 1
    FROM #uidObjects uobj2
    WHERE uobj2.sid = uobj.sid
    AND uobj2.dbName = uobj.dbName
    AND uobj2.uid = uobj.uid
    AND reason IN (4) ) THEN '--?' ELSE '' END
    AS [--Revoke Db Access (Remove Userids)]
FROM #uidObjects uobj
WHERE reason = 2
ORDER BY dbName

SELECT CASE WHEN EXISTS(
    SELECT 1
    FROM #uidObjects uobj2
    WHERE uobj2.sid = lgnc.sid
    AND reason IN (6, 8, 10) ) THEN '--!' ELSE '' END +
    'EXEC sp_revokeLogin ''' + ntLogin + ''''   --or chg to sp_denyLogin
    AS [--Revoke (or Deny) Logins]
FROM #loginsToCheck lgnc
ORDER BY ntLogin
0
 
JordanKingsleyAuthor Commented:
Absolutely Incredible !!!! Can we ask you a few questions on this tomorrow?
0
 
Scott PletcherSenior DBACommented:
Sure.  Just be sure to run it at least once first so you're familiar with the output :-) .
0
 
JordanKingsleyAuthor Commented:
I just ran it and it's the output I have a few questions about. Are you going to be on much longer?
0
 
Scott PletcherSenior DBACommented:
Yeah, 'nother 20 mins. or so.
0
 
JordanKingsleyAuthor Commented:
There are six result sets. The first, shows the orginal 135 orphan users. The second, has "Hand?" as the first output column with 139 rows of users. The third (no output) Change db owners, the fourth (no output) Change object owners, the fifth Revoke db access (remove user ids) 139 rows, and the sixth Revoke or deny logins 135 users.

I've never seen  something like this. When you're on tomorrow can you explain in a bit more detail exactly what it is doing?

And again, WOW !!!!! That deserves a lot more than 500 points. Thank you so much Scott !!!!
0
 
JordanKingsleyAuthor Commented:
Now that they've been revoked, is there a way to quickly remove them completely from the logins? (from an output set?) I'll ask tomorrow, you've gotta be pretty tired.
0
 
Scott PletcherSenior DBACommented:
>> And again, WOW !!!!! That deserves a lot more than 500 points. Thank you so much Scott !!!! <<

Thanks so much! ::blush::  It at least deserves an "A" then, right :-)


>> The first, shows the orginal 135 orphan users. <<

Must be careful with terminology -- orphan *logins*.


>> The second, has "Hand?" as the first output column with 139 rows of users. <<

These are the specific issue(s) involved with removing the logins.  Possible issues are:
(1) user owns a db :: resolved by changing the db owner, done in output (o/p) script
(2) user has access to a db :: resolved by revoking access, done in o/p script [but cannot occur until every potential ownership issue in the db has been resolved]
(3) user owns an object in the db :: for many objects, resolved by changing the object owner, done in the o/o script *except* for:
    (3a) user-defined data types and
    (3b) roles
    whose owners cannot be directly changed
(4) user has granted permission(s) to other user(s) :: ?not yet coded to resolve in an automated way, *no* code in o/p to deal with it
0
 
Scott PletcherSenior DBACommented:
>> The third (no output) Change db owners <<

That's good -- that means none of the orphaned logins own any dbs.


>> the fourth (no output) Change object owners <<

That's even better -- none of the orphaned logins own any db objects.


>> the fifth Revoke db access (remove user ids) 139 rows <<

Before the login can be removed, *all* related users must be removed


>> and the sixth Revoke or deny logins 135 [logins] <<

Yep, the final step, completely remove the orphaned logins.  


Interesting that there are 139 users for only 135 logins ... would be curious to know if the same login was added under different user names to the same db??
0
 
JordanKingsleyAuthor Commented:
None of these users can be owners. They are strictly added through the application. Only two of us dbas can possibly ever be owners. I'll post a part two for discussion tomorrow if that's ok with you. Good night and thanks again !!! :^)
0
 
Scott PletcherSenior DBACommented:
Btw, "Hand?" means:

"You will need to deal with this by hand [stand-alone yourself] because it is *not* included in the script."

An ! means that's definitely true, a ? means there is a "try" in the script to deal with it but it might not work, so you should check it first before running.
0
 
JordanKingsleyAuthor Commented:
Hello Scott and everyone. I was thinking about the last snippet in the script and was wondering if the sp_revokelogin can be replaced with sp_droplogin. It would be:

SELECT CASE WHEN EXISTS(
    SELECT 1
    FROM #uidObjects uobj2
    WHERE uobj2.sid = lgnc.sid
    AND reason IN (6, 8, 10) ) THEN '--!' ELSE '' END +
    'EXEC sp_DropLogin ''' + ntLogin + ''''   --or chg to sp_denyLogin
    AS [--Revoke (or Deny) Logins]
FROM #loginsToCheck lgnc
ORDER BY ntLogin


Wouldn't this finish the clean up process?  
0
 
JordanKingsleyAuthor Commented:

Correction


SELECT CASE WHEN EXISTS(
    SELECT 1
    FROM #uidObjects uobj2
    WHERE uobj2.sid = lgnc.sid
    AND reason IN (6, 8, 10) ) THEN '--!' ELSE '' END +
    'EXEC sp_DropLogin ''' + ntLogin + ''''   --or chg to sp_denyLogin
    AS [--Drop Orphan Logins]
FROM #loginsToCheck lgnc
ORDER BY ntLogin
0
 
Scott PletcherSenior DBACommented:
Yes, you're quite right, *depending on how the #loginsToCheck table is loaded*, the command may need to be "sp_dropLogin" rather than sp_"revokeLogin".

Since sp_validateLogins is used for the specific code above, and it only outputs *domain* accounts, the o/p code needs to be "sp_revokeLogin".

However, the method used to load the "#loginsToCheck" was deliberately written to be *irrelevant* to the rest of the code [although I should have picked a more generic name than "ntLogin" :-), so you may want to change that].  That is, you could load any types of logins via any method into #loginsToCheck and the rest of the code should work, *although*, as you have noted, it *will* require changing the final command.

Probably need to add a flag column to the login table to indicate which type of login each row is for.
0
 
JordanKingsleyAuthor Commented:
One last question for you, can I put this script into a scheduled job? Do you have any advice for us on that? For instance, in one step? Multiple steps?

This has been a great lesson in the use of temp tables and a major league tool in our script vaults. Thank you so much once again Scott !!! :^)
0
 
Scott PletcherSenior DBACommented:
Yes, it can be put into sp(s) and/or job(s).  I strongly suggest using two: one to load the #loginsToCheck table, and the other to process it.  So this code becomes one step:

IF OBJECT_ID('tempdb.dbo.#loginsToCheck') IS NOT NULL
    DROP TABLE #loginsToCheck
CREATE TABLE #loginsToCheck (
    sid VARBINARY(85) NOT NULL,
    ntLogin SYSNAME NOT NULL
     )
SET NOCOUNT ON
INSERT INTO #loginsToCheck
EXEC master.dbo.sp_validatelogins


With the rest of the code into a sp; a second step then runs that sp.  

*NOTE* though, that you will have to change the code to make it *execute* -- the current code only gens the code, it doesn't run it.  I don't feel safe "auto-running" code like that, I want to check it first.  But you could modify the code to run the commands instead of, or as well as, displaying them.  Personally, I suggest an input param that "tells" the sp whether to: (1) display only (2) exec only (3) display and exec the code generated.


Then, in the future, if you develop a method other than sp_validateLogins to identity logins that need checked, you change the first part of the code that loads the #check table but then run the "std" sp containing the other code.

For example, you get a phone call and are asked to check on the effects of removing a specific login.  You create the temp table, load that name into the table, then EXEC the std sp.
0
 
JordanKingsleyAuthor Commented:
My programming isn't that great yet. Can you give me an example of how to display and exec ?
0
 
JordanKingsleyAuthor Commented:
(quick example, you've already knocked yourself out with this one)
0
 
Scott PletcherSenior DBACommented:
SORRY, I'll have to pass on that one for now, getting busy here.

But you could post another q for relatively few points (maybe ~200) and someone will no doubt provide a good way to either print and/or exec the cmds generated above :-) .
0
 
JordanKingsleyAuthor Commented:
It's ok, I'm pretty sure I know how to do it. Thanks again so much for all of your help Scott !!! This one is invaluable to many of us ! :^)
0
 
Scott PletcherSenior DBACommented:
I know I'll be using it *soon*, since we're in the process of consolidating servers :-).
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 12
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now