Logins without any server role or users mapped to them
-
I am working on performing a cleanup in LOGINS.
I am using the following code to identify the LOGINS that can potentially be removed
CREATE TABLE #mappedlogins (
loginname nvarchar(2000),
username nvarchar(2000),
dbname nvarchar(2000)
)
exec sp_MSforeachdb '
use [?]
insert into #mappedlogins (loginname, username, dbname)
select sp.name AS LoginName,dp.name AS UserName, ''?''
from sys.server_principals sp
left join sys.database_principals dp
on sp.sid= dp.sid
where sp.type <> ''R'' AND sp.name NOT LIKE ''##%'' and dp.name is not null
'
select * from sys.syslogins where
--isntname = 1 AND
[sysadmin] = 0 AND
[securityadmin] = 0 AND
[serveradmin] = 0 AND
[setupadmin] = 0 AND
[processadmin] = 0 AND
[diskadmin] = 0 AND
[dbcreator] = 0 AND
[bulkadmin] = 0
and name not in (select loginname from #mappedlogins)
and name not like '##MS_%'
drop table #mappedlogins
Is there any way that a LOGIN that meets the following conditions can have any access in SQL Server? (ie "Am I going to break something if I delete them :)"):
- There is no USER in any database that is mapped to the LOGIN
- The LOGIN has no server roles (sysadmin, serveradmin, dbcreator etc.)
I do know that there is always a remote chance that the login is used in some sort of automation where it is temporarily given access and is then removed afterwards.
Any thoughts would be appreaciated.
-
An exception is windows groups existing as users in databases and members of the group existing as logins in the server. In this case user and login won't match, but the login will be granted access to the database.
In these cases, to be sure, you should impersonate the logins you want to drop and test whether IS_MEMBER('windowsGroupName') returns 1 or 0.
-- Gianluca Sartori
-
spaghettidba (2/26/2016)
An exception is windows groups existing as users in databases and members of the group existing as logins in the server. In this case user and login won't match, but the login will be granted access to the database.In these cases, to be sure, you should impersonate the logins you want to drop and test whether IS_MEMBER('windowsGroupName') returns 1 or 0.
Thank you for pointing this out.
Just to clarify your scenario:
There is 2 Active Directory objects:
Security Group: DomainA\GroupA
User: DomainA\UserA
UserA is member of GroupA
There is 2 LOGINS in SQL Server:
DomainA\GroupA
DomainA\UserA
There is a database [Database1] where database USER 'DomainA\GroupA' is mapped to the 'DomainA\GroupA' LOGIN.
The LOGIN 'DomainA\UserA' has no database USER in any other database on the INSTANCE and no INSTANCE level roles.
Are you implying that I cannot DROP the LOGIN 'DomainA\UserA' without removing that users access to the instance and [Database1]?
-
Exactly.
Moreover, you don't even have to have a login for [DomainA\GroupA] in the server.
Users such as [DomainA\GroupA] can be created in a database without a mapping login, but they grant access to logins that are group members in AD
-- Gianluca Sartori
-
Thank you for clarifying.
Our security strategy is quite the opposite though.
I want to grant access primarily using Windows security groups, and do not want to have individual users as neither database USERS or SQL LOGINS.
If Windows group exists as both a LOGIN on the instance level and as a USER on the database level mapped to that LOGIN - all Windows users that are member of that group will have access.
Anyway that is a bit off topic.
Thank you for your thoughts on the matter.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply