How Do I See the List of Users Allowed on a MS SQL Server Database

I want to know the users that are allowed access to a MS SQL Server database. I only want a specific database. Is there a user list hidden somewhere?

What are the ways to go about doing this? Is there an app or through a SQL query?

ANSWER

Use Microsoft SQL Server Management Studio. That is the fastest way to do it. Find the database from the explorer menu on the left-hand side. Expand the folder icon. Go to the Security folder, then click on Users next. That’s it!

If you’re using a different SQL client and you can’t see Security > Users from the GUI. Then your other option is through a SQL query.

This is the query below. More details at this page here: https://www.joseyamut.xyz/2020/08/06/get-user-list-in-a-microsoft-sql-server-database/.

USE <database_name>;
SELECT name AS username,
       type_desc AS type,
       authentication_type_desc AS auth_type
FROM sys.database_principals
ORDER BY type;

Happy user hunting! =)

Differences in the queries

How do i explain the following different queries like i am teaching a 5 year old kid?

 i) SELECT suser_sname(owner_sid) as 'Owner', state_desc,name as DatabaseName, database_id FROM sys.databases
 ii) SELECT * FROM sys.server_principals
 iii) SELECT * FROM sys.sysusers
 iv) SELECT * FROM sys.sysmembers
 v) SELECT * FROM sys.databases
 vi) SELECT * FROM SYS.SYSLOGINS

Go to Source
Author: teo93

SQL Server maintenance plan DTS_W_MAXIMUMERRORCOUNTREACHED

I have an SQL server running on 11.0.5058.0.
I created a backup Maintenance Plan but I’m getting the following error:

Executed as user: MyDomaindbaAcct. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:45:20 AM Error: 2020-07-06 11:45:21.57 Code: 0xC00291EC Source: {39010E7A-E7B3-462C-A418-5AD339537AC2} Execute SQL Task Description: Failed to acquire connection “Local server connection”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2020-07-06 11:45:21.57 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2020-07-06 11:45:21.76 Code: 0xC0024104 Source: Back Up Database (Transaction Log) Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an “out” parameter. End Error Error: 2020-07-06 11:45:21.76 Code: 0xC0024104 Source: {34EEAB12-81DB-4631-9850-2E01D2D2E0F6} Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an “out” parameter. End Error Warning: 2020-07-06 11:45:21.77 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:45:20 AM Finished: 11:45:21 AM Elapsed: 1.437 seconds. The package execution failed. The step failed.

I raised the Maximum error Count to 10 for the subplan (Sequence) and set the TransactionOption to required.

The SQL Server and Agent services are running on MyDomaindbaAcct. I know not best practice, but trying to figure what’s going on here.

Go to Source
Author: Rick