[SCRIPT] – Listing Database Users and Roles

I am a fan of not reinventing the wheel. What that means? Well, If I need to do something, which usually involves creating a script, I try first to do a quick search on Google and see if someone already had this idea.

Looking at the title of the article you might say: Oh, that’s easily done with dbatools, which in fact it is true. However, my use case this time was a bit different than the conventional, because I want to expose that dynamically in my Python/Django Internal tool that exposes SQL Server through API’s, so the need for a TSQL script is imminent.

The goal result that I need is:

 

User Name User Type Create Date Roles
User001 SQL_USER 2018-09-10 14:00 db_datareader
User002 WINDOWS_USER 2018-01-01 13:00 db_datareader;db_datawriter;db_ddladmin

 

Honestly saying, I saw a few solutions, not exactly as I wanted, but the main thing was that they involved the creation of temporary tables and some very messy solution that I personally didn’t like.

So, for those reasons, I personally ended up creating the below script.

image
Source: https://gist.github.com/marcosfreccia/0e6a7b1de7eb644d61a4c7f3b5e86efd

The execution of this query produces the following result.

image

Enjoy!

Marcos Freccia
Data Platform MVP

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on September 13, 2018, in Dicas, T-SQL and tagged , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: