Skip to main content
Share Your Experience: Take the 2024 Developer Survey
37 votes

What does a RED X on a database user mean?

It does not mean the user is disabled (you can only disable logins), it means the user does not have connect privileges to the database. I'm not sure exactly how your users were created, but the ...
Aaron Bertrand's user avatar
27 votes
Accepted

Why storing users passwords at all?

Because for many applications, they don't want individual user accounts connecting to the database. The users/passwords/rights/permissions is all handled at the application layer, and a single ...
BradC's user avatar
  • 9,964
17 votes
Accepted

Username length restriction in different MySQL environments

The maximum length for a user name in MySQL does depend on the version, as stated in the manual: MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8).
Andriy M's user avatar
  • 23k
16 votes

cannot execute as the database principal because the principal does not exist - DB Restoration

When you restore a database it contains database users. These users are transported with the database, whenever you perform a backup and a restore to a different SQL Server. On your source and target ...
John K. N.'s user avatar
  • 17.9k
16 votes
Accepted

Is there a simple way to add a read-only user?

Could someone offer the simplest way--the way that alters the database the least-- to add a login that has read-only access? create login pbi_reader with password = '<your strong password>' ...
David Browne - Microsoft's user avatar
15 votes
Accepted

How do I translate a Windows SID to an SQL Server server_user_sid?

SIDs in the form of 0x01020000000000052000000021020000 are not "SQL Server" SIDs. That is simply the underlying binary value of the SID. Another form it can take (and still be the same value) is the "...
Solomon Rutzky's user avatar
15 votes
Accepted

How to set statement timeout per user?

Yes, Postgres allows settings per user or even per database and user, including statement_timeout: ALTER ROLE foo SET statement_timeout = 12345; -- milliseconds Related: How does the search_path ...
Erwin Brandstetter's user avatar
11 votes

Difference between database vs user vs schema

To begin to understand the difference I think we must start saying that this nomenclature was a mistake from Oracle. I assume that if you have an ERP called TRITON you want your database to be named ...
Glauco Guerrero's user avatar
10 votes

How to get user-specific settings in PostgreSQL?

Unfortunately pg_shadow does not show roles with no LOGIN privilege. I find it more useful to use @dezso hint view in the following query: SELECT coalesce(role.rolname, 'database wide') as role, ...
Zorg's user avatar
  • 301
9 votes
Accepted

postgres create limited user that can select tables and columns of those tables

You can set column level privileges to very specifically (and narrowly) allow otherwise unprivileged users to access data. In your case, it is two catalogs: pg_attribute and pg_type. I have a user ...
András Váczi's user avatar
8 votes

Difference between database vs user vs schema

Data Base. The Data base is a collection of data,where actually data stored. Its a physical Memory Component Connected to a Computer Installed with DBMS Software to manipulate the data in that Memory ...
SanthoshReddy's user avatar
8 votes

Should I disable the guest user on system databases

No you shouldn't disable the guest account on the system databases. You also shouldn't disable it on the user databases. This will stop anyone from using SQL Server Management Studio to connect to ...
mrdenny's user avatar
  • 27k
8 votes

Why storing users passwords at all?

The dividing line is getting a little bit fluffy at that point, but I would consider users and permissions at the database level to be part of the schema not part of the data, and in general ...
David Spillett's user avatar
8 votes

Disable multiple logins in one step

No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL: DECLARE @sql nvarchar(max) = N'', @cmd nvarchar(550) = N'ALTER LOGIN $$$ ...
Aaron Bertrand's user avatar
8 votes

What does a RED X on a database user mean?

I want only to make an addition to Aaron Bertrand's answer reguarding this one: It means the user does not have connect privileges to the database (you can't disable users, only logins). I'm not ...
sepupic's user avatar
  • 11k
8 votes
Accepted

Why and how and where to use user without login

Imagine that you have somebody running an app, and this requires certain privileges. You do want to be able to log who is doing what and see who are logged in to your SQL Server. So each person need ...
Tibor Karaszi's user avatar
7 votes
Accepted

Postgres Large Objects & Multiple Users

One option is to use SET ROLE command after you open a connection from your application to the databaserole: SET ROLE databaserole; In such way any object (including large objects) created within ...
MatheusOl's user avatar
  • 2,539
7 votes

Compare two users permissions on SQL Server database?

Here are two queries I have used to compare permissions between database users. The first shows the database roles (if any) to which the database user has membership. The second shows individual ...
Dave Mason's user avatar
7 votes
Accepted

Is there any way to prevent a PostgreSQL user to change his/her own password?

AFAIK, there is no way to prevent a user from changing his/her password. It's been discussed (quite long ago) on ocassion on PostgreSQL mailing list, and all the answers I remember were basically &...
joanolo's user avatar
  • 13.5k
7 votes
Accepted

Cross-database view - Not able to access database under the current security context

First things first: DO NOT ENABLE TRUSTWORTHY!! There is absolutely no reason to open up such a large security hole. (note: msdb has TRUSTWORTHY enabled and that is fine as it is a Microsoft-supplied ...
Solomon Rutzky's user avatar
7 votes
Accepted

Which characters are allowed in MySQL/MariaDB user account names?

User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running: MariaDB [(none)]> SHOW ...
jynus's user avatar
  • 14.9k
7 votes

Switch user to 'postgres' user on macOS results in "su: Sorry" error

The EBD installer asks for a password during the installation but it is meant for the database user, not the postgres Unix user, which doesn't have a password. This is mentioned in Postgres, ...
Daniel Vérité's user avatar
7 votes
Accepted

How do I force the delete of users from my database?

It seems the user that you are trying to drop has played a role of grantor and hence you need to revoke those accesses before performing drop of user. Try running below command to get details of ...
Learning_DBAdmin's user avatar
6 votes

Find orphaned users

I wanted to first THANK Mark for posting the script. It saved me a bunch of time writing it from scratch. I modified it a little bit since I ran into the issue where i received an error stating the "...
dbafollower's user avatar
6 votes

How to get all roles that a user is a member of (including inherited roles)?

Here is my take on it. It works for one specific user or all users. select a.oid as user_role_id , a.rolname as user_role_name , b.roleid as other_role_id , c.rolname as other_role_name from pg_roles ...
Alexis.Rolland's user avatar
6 votes

Assign db_owner to all users at once

I'm going to start by saying this is a bad, bad, bad idea. Does everyone on the instance need the ability to drop any database they want? (probably without a backup) Or truncate the tables? Or ...
Kenneth Fisher's user avatar
6 votes
Accepted

Assign db_owner to all users at once

I would use two cursors. The first runs through all databases - except sys dbs. For each database the logins will be fetched and added to the db_owner role. If you have hundreds of dbs/ logins, I ...
Joe Platano's user avatar
6 votes

Why storing users passwords at all?

Restating the question Why storing users passwords at all? The simplest answer is that you have to do so. You still store passwords in your alternative method. It's just that you use the ...
mdfst13's user avatar
  • 161
6 votes
Accepted

What is the significance of the principal_id in sys.schemas?

It should be the default "owner" of all objects in the schema. Schema-bound objects have a principal_id column that is NULL by default, in which case it uses the principal_id of the Schema that the ...
Solomon Rutzky's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible