Find database users mapped to db_owner role

05 May,2015 by Jack Vamvas

Question: I want to list all database users on a database that are mapped to the db_owner role.

How can I create a list of database users including listing whether they are a sql user or a windows user ?

The reason for listing the users is part of a SQL Server Security Risk Analysis project

Answer: Creating a list of database users associated with the roles can be achieved using a combinations of views:

sys.database_role_members

sys.database_principals

The script below lists all database users with db_owner. But if you'd like to check the current database user you can Check if current logon is the database owner – SQL Server 2000 – 2005 – 2008 - 2012 - is_member  

 

 

Script to find database users with db_owner role

 

USE my_database
GO
SELECT  members.name as 'members_name', roles.name as 'roles_name',roles.type_desc as 'roles_desc',members.type_desc as 'members_desc'
FROM sys.database_role_members rolemem
INNER JOIN sys.database_principals roles
    ON rolemem.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals members
    ON rolemem.member_principal_id = members.principal_id
	where roles.name = 'db_owner'
ORDER BY members.name


Read More on SQL Server security topics

How to create a SQL Server Security Audit

SQL Server Security Policy

Powershell sql server security audit
Stored Procedure Checklist

 

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find database users mapped to db_owner role


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer