As an audit requirement, we're trying to get a list of all SQL users (across all databases) to our IT audit team. We can do this simply enough logging in as a user with sysadmin role, but we'd prefer not to give that role to our automated report writer user. So, we're trying to select from sql_logins and all we get is sa and the report writer user (instead of the 91 users we should get). We've tried everything we know -- granting permission to sql_logins directly to the user, setting up a new group with permissions to the view & adding the user to it, etc. All to no avail. Any suggestions? Is this no longer possible? Thanks in advance.
GRANT VIEW ANY DEFINITION TO ReportUser;
One Orange Chip
Try this query given below.
* from sys.server_principals where type in ('U','G','S')
SQL Server MVP
Viewing 3 posts - 1 through 2 (of 2 total)