35
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 ...
- 180k
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 ...
- 9,834
25
votes
View active MySQL connections per user
What you need is a breakdown by user and hostname along with a total
SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
SELECT user usr,LEFT(host,LOCATE('...
- 181k
16
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).
- 22.8k
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 "...
- 68.3k
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 ...
- 172k
13
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 ...
- 17.3k
13
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>'
...
- 45.8k
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 ...
- 111
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,
...
- 301
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
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 ...
- 32k
8
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 ...
- 30.9k
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 $$$ ...
- 180k
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 ...
- 10.6k
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 ...
- 17k
7
votes
Accepted
Do not have permission to use 'dbo' when creating a view
Your user should have the permission to modify the dbo schema. You can accomplish that by assigning the permission to a role the User is a member of:
So its like:
CREATE ROLE Limitedaccess;
GO
...
- 7,129
7
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 ...
7
votes
Who is the person using a SQL login?
No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server can only record the information it has ...
- 180k
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 ...
- 2,509
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 ...
- 875
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.3k
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 ...
- 68.3k
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 ...
- 14.7k
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, ...
- 30.8k
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 ...
- 3,894
6
votes
Accepted
Can I create a new user in MS SQL from windows and provide the SID
No, when creating a login from Windows, the SID is the Windows-provided one.
There is consequently no need to remap users; they automatically assume the same SID across servers within the same domain....
- 69.7k
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 "...
- 61
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 ...
- 233
Only top scored, non community-wiki answers of a minimum length are eligible