Permissions to read SQL integrated and AD logins

Question

Hi.

For reporting use, I need to setup a login which have the ability to read all the logins on a SQL server (sql integrated, AD users and AD groups), and which databases they are granted rights in.

I'm struggling a bit to find the minimum set of permissions to assign to the login. With 'datareader' permissions on the master database I only get the built-in accounts. And I would prefer not to have to set permissions on single stored procedures.

Any help would be appreciated.

Cheers,

Søren

Answer 1

You can get all logins from DMV sys.server_principals (Transact-SQL); see the part "Permissions" in that article.

Answer 2

Hm, as I can read, the only option is to grant the 'ALTER ANY LOGIN'

Is there a way to create a custom role, which grants read on all logins (current and future)?

Thanks,

Søren

Answer 3

Hi @Søren Ougaard Povelsen ,

> With the view definition permission I can see all logins, but only the existing. If I create a new login, it is not included.

Did you give the view definition permission to new login in your custom role? Please check this. I test in my environment, the new login will not have the view definition permission automatically in my custom role. We need to give the permission manually.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 4

Hi @CathyJi-MSFT

I created a new server role, which per default has read permissions on all logins.
As I understand it, giving read permissions to a view will not solve it, as the role does not have permissions to the underlying data.

But as I can see, I cannot check that the role should include any future logins (screendump attached).
Is there another way to giving permissions to all logins?

Cheers,
Søren

Answer 5

Hi @ SrenOugaardPovelsen-6966,

> I cannot check that the role should include any future logins (screendump attached).

Please click the plus sign next to login as below screenshot. Then click the login that you want to give the permission.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.