40
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 ...
- 182k
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 ...
- 10k
18
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>'
...
- 47.6k
17
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 ...
- 18.3k
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 "...
- 69.5k
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 ...
- 182k
12
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 ...
- 121
11
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,
...
- 311
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 ...
- 31.6k
9
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 ...
- 17.9k
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 ...
- 27k
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 ...
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 ...
- 32.4k
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 $$$ ...
- 182k
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 ...
- 11.2k
8
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 ...
- 3,904
8
votes
Accepted
Can I GRANT SELECT on all schemas in a database?
From PostgsqlSQL v14 on, you can grant the user the pg_read_all_data predefined role.
Other than that, you will have to grant USAGE on all schemas and SELECT on all tables individually.
GRANT SELECT ...
- 56.4k
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 ...
- 885
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 &...
- 13.6k
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 ...
- 69.5k
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 ...
- 15k
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, ...
- 32.3k
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 ...
- 169
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 ...
- 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 ...
- 69.5k
6
votes
Accepted
Switch user to 'postgres' user on macOS results in "su: Sorry" error
sudo su postgres
Having found this closed Question on Stack Overflow and its duplicate, I will post here the solutions found there and there.
➥ You must use the sudo command combined with the su ...
- 11k
6
votes
Find queries executed by an user in sql server
You would need to start logging account activity of accounts that you don't trust to a table for a complete list. All of the other options (like querying dmvs) will give you temporal data and there's ...
- 421
5
votes
Should I disable the guest user on system databases
No, don't disable the guest account within the system databases.
See https://blogs.msdn.microsoft.com/batuhanyildiz/2013/03/02/guest-user-account-in-sql-server/
and the quote:
The recommendation ...
- 1,381
5
votes
Create a user with privileges to create users
The privilege you are looking for is CREATE USER privilege.
GRANT CREATE_USER on [db].[table] to <user_who_will_have_that_privilege>@<host>
Example :
GRANT SELECT, CREATE USER on *.* ...
- 61
5
votes
Accepted
Create a user with privileges to create users
To be able to create mysql users, you need to be able to insert into the users table in the mysql database. So create your master user and give him write access to the mysql database.
The ...
- 587
Only top scored, non community-wiki answers of a minimum length are eligible