November 5, 2015 at 3:54 pm
We had a recent hire, who is able to log into one of our test databases without any overt configuration. I am trying to back-track to the login under which this person originally logged in. (And they are not setup specifically as a user for this database.)
I am assuming that he's mapped to an AD Group, and I was hoping that ORIGINAL_LOGIN() might give me that group name, but that doesn't work (and I think it's because you can belong to multiple AD groups that are all mapped to LOGINs in the instance, so ORIGINAL_LOGIN() doesn't really help there anyway).
Is there a way that I can tell how this user is getting their permissions to log into the database?
Thanks,
Chuck
November 6, 2015 at 2:42 am
You can run xp_logininfo against all the groups in on the server and note which groups the user is in
exec xp_logininfo 'DOMAIN\Group','members'
November 6, 2015 at 5:40 am
i use this to enumerate all members of all groups on a given server. then you can see when someone shows up in multiple groups.
IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL
DROP TABLE [dbo].[#TMP]
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
USE MASTER
declare
@isql varchar(2000),
@name varchar(64)
declare c1 cursor for
select name FROM master.sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'
AND name NOT LIKE '%$%'
AND name not like 'NT SERVICE\%'
open c1
fetch next from c1 into @name
While @@fetch_status <> -1
begin
select @isql = 'INSERT INTO #TMP EXEC master..xp_logininfo @acctname = ''' + @name +''',@option = ''members'' '
print @isql
exec(@isql)
fetch next from c1 into @name
end
close c1
deallocate c1
SELECT * FROM #tmp ORDER BY [MAPPED LOGIN NAME]
Lowell
November 6, 2015 at 6:29 am
Nice script Lowell, just modified a little to remove the cursor
IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL
DROP TABLE [dbo].[#TMP]
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
USE MASTER
declare
@isql nvarchar(max)
select @isql = replace(
cast(
(SELECT
'INSERT INTO #TMP exec master..xp_logininfo @acctname = '''+name+''',@option = ''members'' '+CHAR(13)+CHAR(10)
from master.sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'
AND name NOT LIKE '%$%'
AND name not like 'NT SERVICE\%'
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),'& # x 0 D ;',CHAR(13)+CHAR(10) --REMOVE THE SPACES HERE, SSC IF TAG PLAYS SILLY BEGGERS WITH XML ENCODING
)
print @isql
execute sp_executesql @isql
SELECT * FROM #tmp ORDER BY [MAPPED LOGIN NAME]
drop table #TMP
November 6, 2015 at 6:59 am
Thanks Anthony! I added that to my snippets; damn i love the teamwork here!
Lowell
November 6, 2015 at 7:06 am
Not a problem.
Noticed that the XML encoding character got stripped out as the IF tag always plays silly beggers with XML encoding
Need (spaces removed)
& # x 0 D ;
Adding in the replace syntax like below
)
AS NVARCHAR(MAX)
),
'& # x 0 D ;',CHAR(13) + CHAR(10)
)
Edit
Modified the script above and added a comment in to detail removing the spaces.
November 7, 2015 at 7:27 pm
My Windows DCs don't like my sqlservr's xp_loginfo (unrelated to this post), but thank you for the script.
A slight rewrite to avoid replace (and cast, somewhat):
IF OBJECT_ID('[tempdb].[dbo].[#TMP]') IS NOT NULL
DROP TABLE [dbo].[#TMP]
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
USE MASTER
declare
@isql nvarchar(max)
select @isql = (SELECT
'INSERT INTO #TMP exec master..xp_logininfo @acctname = '''+name+''',@option = ''members'' '+CHAR(13)+CHAR(10)
from master.sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'
AND name NOT LIKE '%$%'
AND name not like 'NT SERVICE\%'
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
print @isql
execute sp_executesql @isql
SELECT * FROM #tmp ORDER BY [MAPPED LOGIN NAME]
drop table #TMP
November 9, 2015 at 8:49 am
Sorry for the late reply, and thanks for the code. I was playing around with it, and it looks very useful. Unfortunately, though, it doesn't help in this case. Since this particular employee isn't listed as a user in the database, this code can't map him back to a login (which is likely an AD group or a group local to the server).
At this point I am a bit baffled. While the approaches above didn't specifically map this person's database access back to a login, it did present me with the same list of groups I had already checked to see whether he was a member of any of them. Between the AD groups and the local groups, there are only 4 possibilities, so this isn't a huge puzzle, but heck if I've been able to figure it out yet.
Is there any function I can have him run once he's logged in, which would map his access back to any of the logins through which he could have been validated into the database?
--=Chuck
November 9, 2015 at 9:50 am
chuck i'm not a login on any server, i inherit from a group called mydomain\SQL-DBA, nad my name appears in the results of the script i provided.
you should be able to enumerate an individual's groups with the same procedure, regardless of the user being logged in, i think.
EXEC master..xp_logininfo @acctname = 'disney\lowell',@option = 'all' -- Show all paths a user gets his auth from
maybe he's coming in from builtin\Administrators, because it's a server that has that group, and he's a local admin?
my script didn't check that, i don't think.
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply