Skip to main content
Our Tech Ideas

Find all the users for a particular database

Introduction

In SQL Server, managing user access and permissions is crucial for maintaining the security and integrity of a database. Often, there is a need to retrieve a list of user names while excluding certain system roles. In this blog post, we will discuss a script that allows you to accomplish this task efficiently.

Script Overview

The script we’ll explore helps you select user names from the sys.sysusers table in SQL Server. It applies filters to exclude specific system roles such as ‘dbo’, ‘guest’, ‘INFORMATION_SCHEMA’, and ‘sys’. By executing this script, you can obtain a list of relevant user names, which can be further utilized for various administrative and analytical purposes.

SELECT name
FROM sys.sysusers
WHERE issqlrole = 0
  AND name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')

Script Explanation

Let’s break down the script to understand its functionality:

  1. The SELECT statement is used to retrieve data from the sys.sysusers table.
  2. The FROM clause specifies the table from which we want to retrieve data, which is sys.sysusers in this case. This table stores information about user accounts in the SQL Server instance.
  3. The WHERE clause applies filters to narrow down the results. The condition issqlrole = 0 ensures that only non-system roles are considered. The issqlrole column indicates whether a user is a system role or not, with a value of 0 denoting a non-system role.
  4. The AND operator combines multiple conditions in the WHERE clause.
  5. The NOT IN operator excludes specific system roles from the result set. The list of excluded roles is provided within the parentheses and separated by commas.