question

rpegan11 avatar image
rpegan11 asked

Does sp_helprolemember only return active users?

In the results of sp_helprolemember, are only the users/IDs who have access at that moment shown? Or are disabled IDs displayed as well? And lastly, is it possible that an Windows ID that previously had access to a database but has since been completely removed from Active Directory, would still show in the results of sp_helprolemember?
stored-proceduresactive-directory
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
From a quick test, it will continue to show disabled logins. This makes since to me as the user object within the database is a different object than a server login. So even if the server login is disabled, the user object remains intact (and subsequently a member of the role) within the database. If the AD account was explicitly added as a login and then as a database user (and role member) it would remain listed even if the AD account was removed. This is why the best practice is to use AD Groups versus explicit logins if it can be helped. Until you remove the database user and/or remove them from the role, the user will continue to be listed in the results of that system procedure. Hope that helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.