Hot answers tagged

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 ...
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,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('...
RolandoMySQLDBA's user avatar
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).
Andriy M's user avatar
  • 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 "...
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
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 ...
John K. N.'s user avatar
  • 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>' ...
David Browne - Microsoft'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
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
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 ...
dezso's user avatar
  • 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 $$$ ...
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
  • 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 ...
Tibor Karaszi's user avatar
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 ...
KASQLDBA's user avatar
  • 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 ...
SanthoshReddy's user avatar
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 ...
Aaron Bertrand'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,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 ...
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.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 ...
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.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, ...
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
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....
Hannah Vernon's user avatar
  • 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 "...
dbafollower'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

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