July 26, 2017 at 8:21 am
My issue is we removed several users at the server level and left them at the database level. I'm trying to identify which were removed. Having a hard time getting the list of logins that are at the server level only.
July 26, 2017 at 8:29 am
Logins are in sys.server_principals; users are in sys.database_principals in each database. You can join the two to find out which database users are orphaned and/or which logins don't have explicit access to any database.
John
July 26, 2017 at 8:50 am
Using the undocumented procedure sp_MSforeachdb (yes, I know some people don't like it), you could do something like this:
USE OpenGI;
GO
Use master;
GO
CREATE TABLE #Orphan (DBName varchar(50), Orphan varchar(50));
GO
EXEC sp_MSforeachdb 'USE [?];
INSERT INTO #Orphan
SELECT ''?'' AS DBName, dp.name AS Orphaned_User
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp on dp.sid = sp.sid
WHERE sp.principal_id IS NULL
AND dp.Type IN (''U'',''S'')
AND dp.authentication_type_desc != ''NONE''
AND dp.name != ''dbo'';';
GO
SELECT *
FROM #Orphan
ORDER BY DBName, Orphan;
GO
DROP TABLE #Orphan
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 26, 2017 at 9:00 am
Snargables - Wednesday, July 26, 2017 8:21 AMMy issue is we removed several users at the server level and left them at the database level. I'm trying to identify which were removed. Having a hard time getting the list of logins that are at the server level only.
Sorry, confused, you said you removed server level logins but were left with database user (this is the default qaction by the way when deleteing a login)?
Then you say haviong hard time getting list of the logins that are server level, which by all accounts above you've deleted.
If, and this is what i think you mean, you want to get a list of the database users remaining that have no server level login then please see my script belowexec sp_MSforeachdb @command1 = 'USE [?];select DB_NAME() AS DatabaseName, dp.name AS OrphanedDatabaseUser
from sys.database_principals dp
left outer join sys.server_principals sp
on dp.principal_id = sp.principal_id
where dp.type <> ''R'' AND dp.name NOT LIKE ''##%##'' AND sp.name IS NULL'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" š
July 26, 2017 at 9:56 am
if you've added any windows_groups, it's still possible that users have access, and have database access, because they login thru group membership,but don't have an explicit login.
here's a simple cursor to enumerate the explicit windows groups for their users, that might help too:
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
DROP TABLE #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 )
DECLARE @groupname NVARCHAR(256)
DECLARE c1 CURSOR FOR SELECT name FROM master.sys.server_principals WHERE type_desc = 'WINDOWS_GROUP' AND CHARINDEX('$',name) = 0
OPEN c1
FETCH NEXT FROM c1 INTO @groupname
WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
EXEC master..xp_logininfo @acctname = @groupname,@option = 'members' -- show group members
FETCH NEXT FROM c1 INTO @groupname
END
CLOSE c1
DEALLOCATE c1
SELECT * FROM [#TMP]
--WHERE [MAPPED LOGIN NAME] = 'mydomain\Lowell'
Lowell
July 26, 2017 at 10:06 am
left joining server_principals to database_principals will work for SQL Authenticated users, but this query shows some false positives for database users that do have a login which is a windows authentication active directory group. You'd have to check the permissions path for each one of those using XP_LOGININFO, so maybe a script to generate all those commands is:SELECT 'EXEC master..xp_logininfo ''' + dp.name + ''', ''all''' AS sqlcmd
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp on dp.sid = sp.sid
WHERE sp.principal_id IS NULL
AND dp.Type IN ('U')
AND dp.authentication_type_desc != 'NONE'
AND dp.name != 'dbo';
EDIT: d'oh, Lowell beat me to it!
July 26, 2017 at 11:27 am
so question. I have an nt user who has read and write access to a database but that nt user has been removed from the server logins. The user also is part of a group that has read but not write to the same database. The user in this case will only have read to the db correct?
July 26, 2017 at 11:33 am
Snargables - Wednesday, July 26, 2017 11:27 AMso question. I have an nt user who has read and write access to a database but that nt user has been removed from the server logins. The user also is part of a group that has read but not write to the same database. The user in this case will only have read to the db correct?
The user would have both read and write, the group login gives them ability to connect, but since the database user still exists, its permissions are still valid even though the group they login with is more restrictive.
July 26, 2017 at 11:34 am
role permissions are cumulative, so if you have anNT user in two roles, one with readwrite, and one with read only, the effective permissions are the sum....readwrite.
only an explicit DENY would override the cumulative permissions, and that gets ignored if they are sysadmin
Lowell
July 26, 2017 at 1:34 pm
I think u misunderstood. wasn't trying to be tricky just wanted to confirm what I think is the obvious.
If the nt user is orphaned and only exist at database level yet has read\write
That same nt user is in a valid group that has only read then the user will only have read correct? because the nt account user is orphaned.
July 26, 2017 at 1:45 pm
the NT user in that case is technically not orphaned, because the active directory group they are in has a login. The user can still have permissions that the rest of the group doesn't. As Lowell mentioned, the database permissions are cumulative for the user and any active directory groups they are in. I've utilized this before, for example when a supervisor has extra permissions that the rest of the AD group doesn't.
July 26, 2017 at 2:07 pm
disregard. i'll test it out and let u know
July 27, 2017 at 7:19 am
wow. confirmed. if an nt user does not exist is sql at the server levelhowever exists at the database level yet that user is in a group who exists atthe server level then the nt user and it's groups permissions are indeedcumulative even though the windows nt user doesn't exist at the server level.I'm sure u are all saying "duh, that's what I've been saying". This just doesn't seem logical to me. Unfortunately, I'm in the process ofcleaning up our users and converting to groups. Step 1 was to remove the userswe suspected were no longer being used from the server level permissions. Nextstep is to remove them from the database level. Now Iām concerned if we removethem from the database level we will run into connection issues. There are 105distinct users and sql logins that I was planning on deleting at the databaselevel.
I suppose at this point I need to rethink this. Is there another way to identify stale or unused sql/windows logins w/o running a trace for a week?
July 27, 2017 at 8:12 am
Snargables - Thursday, July 27, 2017 7:19 AMwow. confirmed. if an nt user does not exist is sql at the server levelhowever exists at the database level yet that user is in a group who exists atthe server level then the nt user and it's groups permissions are indeedcumulative even though the windows nt user doesn't exist at the server level.
The NT user does exist at the server level, by virtue of being in a group that's mapped as a login. That's the point of adding domain groups as logins, all the members of that group can log in and you don't need to mess with SQL logins when you add or remove members of that group.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply