July 16, 2008 at 9:45 am
We have a process that identifies and removes orphan users. By "orphans" I mean users that were once tied to a login, but no longer are. Orphan users frequently arise when DB backups are restored on different servers, in different environments. Orphans are readily identified by trying to join users and logins.
This works well enough, but a potential problem is that this process cannot tell the difference between an orphan user and one created using the "CREATE USER xxx WITHOUT LOGIN" syntax.
Is there any way to differentiate between an orphan user and one that was created "WITHOUT LOGIN"?
Or is there no difference between them...maybe the WITHOUT LOGIN syntax is simply a way to intentionally create an orphan user?
July 16, 2008 at 10:44 am
As far as I can tell, there isn't a way to differentiate between a real orphaned user and one purposely created without a login.
Here's an interesting blog about the usefulness of the WITHOUT LOGIN option:
http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
Greg
July 17, 2008 at 4:07 pm
Darn. Well at least 2 of us have come to same conclusion.
When we start using "WITHOUT LOGIN" users we're going to have to abandon notion of programmatically identifying and removing orphan users.
Thanks for replying.
July 17, 2008 at 4:41 pm
Mike Good (7/17/2008)
Darn. Well at least 2 of us have come to same conclusion.When we start using "WITHOUT LOGIN" users we're going to have to abandon notion of programmatically identifying and removing orphan users.
Thanks for replying.
I agree that there is no way to differentiate between a user that has become orphaned and one that was created without a log in deliberately, but if you know why you are using "without login" users, you may be able to check specifically for that.
For instance, if you know that in your organization they are used only for "execute as" within procedures, you can programmatically check the procedure text for "execute as " and be fairly confident they became orphaned and are no longer needed if that does not exist.
There may not be a general way to identify unneeded users, but you may be able to find a method that works specifically for your organization.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 18, 2008 at 3:21 pm
Mike Good (7/17/2008)
Darn. Well at least 2 of us have come to same conclusion.When we start using "WITHOUT LOGIN" users we're going to have to abandon notion of programmatically identifying and removing orphan users.
Thanks for replying.
Good naming conventions/standards can help an awful lot here. And although I am not generally in the "heavy-handed DBA" camp, IMHO this is a perfect example of where the DBA should arbitrarily impose a standard. (And an "Exceptional" DBA would also know how to use her organization's Auditors to win any arguments over this.)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 19, 2008 at 2:02 pm
The below stored proc will differentiate between orphan users and and not include those created with the "WITHOUT LOGIN" clause in 2005.
USE master
GO
IF OBJECT_ID('rpt_security_detect_db_orphans') IS NOT NULL
DROP PROCEDURE [dbo].[rpt_security_detect_db_orphans]
GO
CREATE PROCEDURE dbo.rpt_security_detect_db_orphans
AS
SET NOCOUNT ON
/*
NE 6/11/2007 - Detect orphan users in all dbs on a SQL instance; works in both SQL 2000 and 2005.
EXEC rpt_security_detect_db_orphans
8/27/2007 - NE - Modified to detect orphans better per SQL 2005, differentiating non-orphan db users
created using the new SQL 2005 "WITHOUT LOGIN" clause. Based on changes to the "-- HANDLE REPORT --"
section of the "sp_change_users_login" system stored procedure between SQL 2000 and SQL 2005. E.g.,
"USE my_db; CREATE USER my_user WITHOUT LOGIN"
1/8/2008 - NE - Added Windows users/groups in dbs that are orphans (corresponding login does
not exist in SQL).
*/
DECLARE
@dbname varchar(200),
@sql varchar(8000)
DECLARE @temp table
(
dbname VARCHAR(500)
)
CREATE TABLE #dbOrphanUsers
(
DbName varchar(500),
[User] varchar(500)
)
INSERT INTO @temp
SELECT name
FROM sysdatabases
WHERE
category IN ('0', '1','16')
AND
name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
AND
name NOT LIKE 'adventurework%'
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
ORDER BY name
SELECT @dbname = MIN(dbname) FROM @temp
WHILE @dbname IS NOT NULL
BEGIN
SET @sql =
'INSERT INTO #dbOrphanUsers
(DbName, [User])
SELECT
DbName = ''' + @dbname + ''',
[User] =
CASE
WHEN b.[User] IS NULL THEN a.[User] + '' (not an orphan, but db user was created using the new SQL 2005 "WITHOUT LOGIN" clause)''
ELSE a.[User]
END
FROM
(
SELECT DbName = ''' + @dbname + ''', u.name AS [User]
FROM [' + @dbname + '].dbo.sysusers u
WHERE
issqluser = 1
and (sid is not null
and sid <> 0x0)
--and (len(sid) <= 16)
and suser_sname(sid) is null
) AS a
LEFT JOIN
(
SELECT DbName = ''' + @dbname + ''', u.name AS [User]
FROM [' + @dbname + '].dbo.sysusers u
WHERE
issqluser = 1
and (sid is not null
and sid <> 0x0)
and (len(sid) <= 16)
and suser_sname(sid) is NULL
) AS b
ON a.[User] = b.[User]
UNION
SELECT DbName = ''' + @dbname + ''', name
FROM [' + @dbname + '].dbo.sysusers
WHERE suser_sname(sid) is NULL AND isntname = 1
ORDER BY DbName, [User]'
EXEC(@sql)
SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
END
SELECT * FROM #dbOrphanUsers ORDER BY DbName, [User]
DROP TABLE #dbOrphanUsers
GO
July 28, 2008 at 8:34 pm
Thank you StormNorm!!! I am impressed! And thank you for documenting your sources. I guess you ran into the same problem I did, but with more determination.
July 28, 2008 at 8:58 pm
timothyawiseman and rbarryyoung: Thanks for replies. I'm not positive, but I think I like the idea of naming these users differently. Striving to be an excellent DBA....
October 1, 2008 at 2:49 pm
Thanks StormNorm...it really helps...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply