Hot answers tagged

56 votes
Accepted

PostgreSQL roles versus users, grant permissions

A role is an entity that can function as a user and/or as a group. A role WITH LOGIN can be used as a user, i.e. you can log in with it. Any role can function as a group, including roles that you can ...
user avatar
  • 52.2k
33 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 ...
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 ...
user avatar
  • 9,614
24 votes

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

This is a simplified version of Craig Ringer's answer that a non superuser can use directly: SELECT oid, rolname FROM pg_roles WHERE pg_has_role( 'maxwell', oid, 'member'); pg_roles is ...
user avatar
20 votes
Accepted

How to see profile assigned to a user

In order to find out the users and the profile assigned you can use the commands below. DESC DBA_USERS; This will show you all the fields name for which you want to query SELECT USERNAME, ...
user avatar
20 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('...
user avatar
15 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).
user avatar
  • 21.3k
14 votes
Accepted

Feasible to have thousands of users in Postgres?

Yes, it should be fine. You should use connection pooling though, as pg uses a fair amount of memory per connection (about 10MB AFAIK). More than 500 simultaneous connections per box will be a problem ...
user avatar
14 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 "...
user avatar
11 votes
Accepted

MySQL service stops after trying to grant privileges to a user

I think your problem boils down to a misalignment of grants. When you run this query SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; ...
user avatar
11 votes
Accepted

Export all MySQL users

If you are moving the users to another DB Server running the same major version of MySQL, copying mysql.user is not sufficient. If the users have access to specific databases, copying mysql.user ...
user avatar
11 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 ...
user avatar
9 votes
Accepted

ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

Your problem has to do with mysql.user and the way you upgraded to MySQL 5.6 If you look my answer to Cannot GRANT privileges as root, I show you the description of mysql.user from MySQL 4.1 to MySQL ...
user avatar
9 votes
Accepted

how to check list of users present in all postgres database

Users are global to the Postgres instance (aka "cluster"). So there is no such thing as "their respective database name". \du will always show you the same list of users regardless of the database you ...
user avatar
8 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, ...
user avatar
  • 281
8 votes
Accepted

Create postgres schema in db with multi user in AUTHORIZATION

Owned by more than one user: no Accessible by more than one user: yes To give a user (or role) access to the tables in a schema you can use something like this: grant all on schema foobar to ...
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 ...
user avatar
  • 26.9k
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 ...
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 $$$ ...
user avatar
8 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 ...
user avatar
7 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 ...
user avatar
7 votes
Accepted

Comma List vs Multiple Records

Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately. Table GroupMemberRoles GroupID ...
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 ...
user avatar
  • 6,970
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 ...
user avatar
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 ...
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 ...
user avatar
  • 14k
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 "...
user avatar
6 votes
Accepted

Database User not reported as orphaned after Windows login dropped

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check: SELECT p.name FROM database_name.sys.database_principals AS p WHERE [type] IN (N'...
user avatar
6 votes

DROP LOGIN fails on SQL Server 2014

For SQL Authentication logins, this is no different in SQL Server 2014 than in previous versions. I just did this in 2008: CREATE LOGIN dbowner WITH PASSWORD = 'x', CHECK_POLICY = OFF; GO CREATE ...
user avatar

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