Determine database permissions for database users with valid server logins

Question

Hello,

I'm looking to find a script that will provide me with the details of what permissions our current SQL server logins have. I am able to script out the current permissions of our database users. The issue I have is that a lot of the database users no longer have a corresponding SQL server login due to a variety of reasons such as domain migrations & staff leaving the company.

The script used to get the database user permissions is : <credit to Nag Pal MCTS/MCITP (SQL Server 2005/2008) this was taken from the old MSDN forum>

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Thank you

Answer 1

What do you see in the result for a "person" where that person is no longer in the AD group or doesn't exist as a login? As I read that script, it should return a row for the assigned privileges even if the SID isn't in the AD any longer/or not exist as a login.

Answer 2

Hi @Stevan Allen ,

I did some hard work, but unfortunately it did not succeed.
The following article may help you：
T-SQL script to get detailed login permissions

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

Hi there,

maybe dbatools ( www.dbatools.io) can help.
There is one command called
Get-DbaOrphanUser

https://docs.dbatools.io/#Get-DbaDbOrphanUser
That's the 1st step to get all your "affected" users.
With Get-DbaPermission you are able to get the server and database permissions.
https://docs.dbatools.io/#Get-DbaPermission

As well as this command
https://docs.dbatools.io/#Get-DbaUserPermission

Maybe this helps

Regards
Dirk