All Questions
Tagged with permissions security
166 questions
7
votes
1
answer
9k
views
Using AD groups vs roles in SQL Server
What is best practice for SQL Server security?
Using AD Groups that are given rights on schema objects
or should I add the AD Groups to a database role, which is given rights on schema objects?
If the ...
- 1,554
0
votes
1
answer
50
views
SQL Server read-only SystemAdmin account
Is there a way to create a sysadmin like account with only read privilege's.
I know that you can give a lot of grants to an account like view server state, view any definition, view any database etc, ...
- 511
0
votes
1
answer
628
views
Explicitly revoke privileges from a user who is a member of a role that is granted those privileges in PostgreSQL
Let's say I have a user on my PostgreSQL instance called "myuser", and I add it to a new group called "update_all" like this:
CREATE ROLE update_all WITH NOLOGIN ROLE myuser;
I ...
CommunityBot
- 1
0
votes
1
answer
421
views
Does SSRS report user needs database level permission?
I have a separate reporting server and a separate database server. Now on the Reporting server site settings I could see there are some roles and users. Suppose a User BOB has ‘POWER USER’ role ...
CommunityBot
- 1
0
votes
1
answer
32
views
Disabling Login of a User Who Have Scheduled Jobs to Run in Oracle
I have a custom oracle user(let's say monitor_user) which has lots of procedures and jobs that sends e-mail of query results. But for security reasons i want to disable its login to database from an ...
- 127
3
votes
1
answer
620
views
How to set minimal permissions to change SQL Azure Firewall rules
I have a desktop app that connects to SQL Azure database.
Some users have dynamic IP addresses, and it is not possible to get them to use fixed addresses.
I've written an ASP.NET page hosted on the ...
CommunityBot
- 1
1
vote
2
answers
84
views
User can't access a database because he belongs to multiple AD groups
We have an SQL Server 2016 that has 20 different databases on it that are used by different applications and different teams in the company. There are groups of users in Active Directory that combine ...
- 14.5k
0
votes
2
answers
1k
views
ALTER permission on database allows creation of security principals
It appears the ALTER permission granted at the database level allows the principal to which it is granted, to create security principals in the database.
Consider the below example:
/* setup database *...
- 1
0
votes
0
answers
79
views
How to prevent user from connecting via SSMS but allow query via .net application connectionstring?
I have been given task to create a SQL login that can be used to query the db via a .net application connection string.
But the user shouldn't be able to connect to SQL via SSMS.
I have created the ...
- 3,489
9
votes
9
answers
61k
views
Error Authenticating Proxy Account when Executing SSIS job
I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.
These packages have been running for years and the associated steps run via a Proxy Account (...
- 317
0
votes
3
answers
1k
views
Cascading REVOKE not working in MYSQL
I've read about an SQL option of cascading permissions when issuing a REVOKE statement. I've googled for how this option is implemented in MySQL, but haven't found it at all.
This is the docs page ...
CommunityBot
- 1
1
vote
1
answer
56
views
Logical replication security risk with run_as_owner = false
The docs say
If the subscription has been configured with run_as_owner = true, then no user switching will occur. Instead, all operations will be performed with the permissions of the subscription ...
- 56.4k
1
vote
0
answers
30
views
How to securely limit columns in postgres logical replication?
The docs currently have this to say about publications with specified column lists:
The choice of columns can be based on behavioral or performance reasons. However, do not rely on this feature for ...
- 305
1
vote
1
answer
58
views
SAGE ODBC driver - minimum required folder permisions
I have a website which I want to connect to SAGE ACCDATA folder (which resides on a different server but same network to the intranet server) using their proprietary ODBC driver.
For security purposes ...
- 163
1
vote
1
answer
498
views
What is the difference between a securable and permission in SQL Server?
In Microsoft SQL Server you have the possibility to set permissions at server and database level. You are also able to select a server login or database user and then set securables. I am wondering ...
- 984
6
votes
2
answers
1k
views
Sql Server Agent is part of the sysadmin role even tough Sql Server says it's not
I created a Windows account with no extra rights other than domain user and set it as the SQL Server Agent service account with the SQL Server Configuration Manager.
The BoL/MSDN says that this user ...
- 2,201
1
vote
0
answers
101
views
remove unix user named postgres after complete uninstallation of postgresql
I'm running MX Linux 21 and recently installed PostgreSQL-13; I wanted postgres to have read, write and execute access to a certain folder therefore I added the unix user postgres to the group of ...
- 111
1
vote
1
answer
74
views
How to list the permissions needed for a role / user to run a query / view successfully (in an automated manner)?
Suppose I have queries / views like this:
select
internal.companies._cid,
internal.companies.name,
src1.employees.year,
src1.employees.count
from internal.companies
join src1....
CommunityBot
- 1
2
votes
1
answer
1k
views
MySQL least privileges to get database/tables size
I'm using centreon_plugins to monitor some mysql databases. As a best practice I create a dedicated user for monitoring and I give it the least possible privileges.
For simple monitoring I usually ...
CommunityBot
- 1
-1
votes
1
answer
4k
views
How to query the effective permission of sql login (windows and/or sql user)?
How to query the SQL server to fetch the effective permission of the following SQL logins under the Security/Logins:
SQL user
Windows user
Windows group
CommunityBot
- 1
4
votes
2
answers
19k
views
What are SHOWPLAN permission security risks? (MS SQL Server)
I'm having some friction when it comes to showplan permission security.
Say there are 5 databases on a server. And each one has a test/ production split (so 10 total).
There is one sysadmin.
There ...
- 875
1
vote
1
answer
3k
views
Restrict access to a new schema to a specific role despite users having db_datareader?
I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am ...
- 238
4
votes
2
answers
6k
views
Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?
I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to ...
- 229
0
votes
2
answers
158
views
What can malicious postgres db user do to a linux server?
Suppose I created a db user with
create role myuser login password 'xyz';
and allowed typical read and write db permissions.
If a malicious user finds these login credentials (and has access to db), ...
- 3,426
1
vote
1
answer
4k
views
Changing the login name for user dbo
I have a database where the owner is Login1 but the username dbo is assigned to Login - login2. I understand in order to change the login name for the username dbo to sa I will have to use the below
...
CommunityBot
- 1
0
votes
3
answers
2k
views
Unable to grant membership to public role
I'm trying to refresh my dev db with a snapshot from prod. I've restored the production db from the prod server to my dev server just named as development. I'm trying to grant access to the dev db to ...
- 18.3k
1
vote
1
answer
311
views
Unable to restrict user from built in SQL Server Functions
We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect ...
- 69.5k
5
votes
2
answers
3k
views
Recover SA Password - PSTools - Not working
I'm on a new company, and the first thing I saw is that they don't even know how many instances they have. they don't have passwords and the old DBA had no Idea what he was doing.
long story short, I ...
- 90.3k
16
votes
4
answers
20k
views
Security and Performance implications of "View Server State"
This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the ...
- 69.5k
1
vote
1
answer
394
views
How to remove user from login (user is already part of group) and keep the jobs?
I have a sql login for windows group.
One of the members of ths group was also added as a sql login. This user has 1 job in which he is registered as the owner.
I want to remove this user login ...
- 70.5k
0
votes
1
answer
134
views
Does deleting the login retain the permissions given via group membership?
I have a windows group account added as login in sql server. The group contains around 100 members.
The above windows group login is given specific database level permissions like db_datareader, ...
- 70.5k
1
vote
1
answer
138
views
What is effective permission if windows user is added as login as well as via group membership; with only public permission given via the user login?
Link: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver15#principals
Remember that a ...
- 70.5k
-1
votes
2
answers
162
views
Extremely safe Postgres table permissions
Let's say that I have four tables in my Postgres database, two of which are private and contain highly sensitive information (private1 and private2), and two that contain information I want to allow ...
- 9,480
0
votes
1
answer
165
views
is it safe to grant permission to all users in Microsoft sql server
I have a global production server and database, as I all users should have access to it by remote desktop.
Is it possible to grant NTFS permission on all database files (mdf+ldf files) to all users? ...
- 388
1
vote
1
answer
5k
views
What are the user privileges required to for an ssrs report owner to 'send email'
Yes, this question is a duplicate to:
SSRS - rsAccessDenied error but that question is 2
years old and unanswered...so I thought I might try again.
Our DBA has recently departed leaving behind a ...
- 1
3
votes
1
answer
2k
views
Postgres permission for EXPLAIN
I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?
- 3,094
27
votes
8
answers
106k
views
A query that lists all mapped users for a given login
When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:
I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
- 182k
0
votes
1
answer
2k
views
How is the "Adjust memory quotas for a process" user right assignment used in SQL Server 2019?
I am looking at the Configure Windows Service Accounts and Permissions doc (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?...
- 19.1k
10
votes
1
answer
10k
views
Is there a difference between granting SELECT to a user and adding them to the db_datareader role?
I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database. I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level.
...
- 24.3k
1
vote
2
answers
5k
views
How to grant permissions to allow anything inside a SQL Server schema?
I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:
Select, update, insert, and delete permissions on any object ...
- 69.5k
1
vote
1
answer
3k
views
SQL Server Grant CRUD to Users on One Schema
I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside ...
- 10.4k
11
votes
2
answers
2k
views
Break the link between a Database User and SQL Server Login
When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User.
This is normally achieved with one of the following procedures.
Deprecated ...
- 18.3k
3
votes
1
answer
8k
views
Login failed for user Error: 18456 State: 10
In one of my vb.net applications I am getting the error Login failed for user 'user'.
But the user has full permission (sysadmin). Also this error happens intermittently.
Error log has the following:
...
- 182k
4
votes
1
answer
23k
views
Permission in schema for user
I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?
- 182k
1
vote
1
answer
300
views
Allows to select on view, while hiding its definition
I need to create a user in PostgreSQL with the least privileges possible. So, the user would only be allowed to do queries on specific tables and views. That user should not be allowed to get any ...
- 56.4k
8
votes
3
answers
53k
views
Creating a database under a different owner
I'm trying to set up a role in Postgres which can
a) Create a new Role
b) Create a new Database
c) Make that new Role the owner of the database
d) Have no other rights (as far as is possible!)
I have ...
2
votes
2
answers
8k
views
Granting minimum select permission on system "sys" tables in every database
Our development team has created some SSRS reports that retrieve metadata only (no 'real' data) from every database. I am trying to lock down the service account running these reports to the minimum ...
- 2,696
5
votes
1
answer
12k
views
How to grant permission to execute stored procedure in master database from another database
Our developers have a domain account used by their application that needs to be able to execute stored procedures created in the master database using the "sp_" naming convention from other databases ...
- 16.5k
2
votes
1
answer
2k
views
Permissions for SSIS/SQL Server agent jobs for multiple user groups
What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only? For example, I have 3 folders in the Integration Services catalog. Dept_A, ...
CommunityBot
- 1
3
votes
1
answer
280
views
Allowing non-privileged users to cause privileged code to run
Context:
Our SQL server is used for teaching, and we need to be able to automatically create databases for the students on demand and in real-time. We also need the students to be able to delete ...
- 69.5k