Questions tagged [permissions]

Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.

Filter by
Sorted by
Tagged with
70 votes
1 answer
91k views

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go. The application currently consists of a command ...
Jim Dennis's user avatar
8 votes
4 answers
59k views

Cannot GRANT privileges as root

Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
maaartinus's user avatar
6 votes
2 answers
5k views

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application checks the status of the SQL Server Agent to see if it is running ...
Ashish's user avatar
  • 727
3 votes
1 answer
9k views

MySQL service stops after trying to grant privileges to a user

I am using MySQL 5.6, I was trying to create a super user for specific database but I got ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%' Then I thought maybe there is already ...
fhuseynli's user avatar
  • 195
69 votes
4 answers
76k views

Created user can access all databases in PostgreSQL without any grants

I must be missing something with regards to setting up PostgreSQL. What I'd like to do is create multiple databases and users that are isolated from each other so that a specific user only has access ...
mikeplate's user avatar
  • 793
1 vote
1 answer
8k views

routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

I have developed my own stored procedure, that through a LINKED SERVER, backups the SSAS databases according to a table value parameter. the code for the stored procedure is below. the problem: it ...
Marcello Miorelli's user avatar
57 votes
2 answers
73k views

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...
8kb's user avatar
  • 2,629
13 votes
3 answers
27k views

How to refresh AD security group on Sql Server permissions

I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP. I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems ...
D_Bester's user avatar
  • 233
12 votes
1 answer
30k views

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

I have a job that runs an SSIS package. Currently the owner is a proxy account. I can run the job manually from a sys-admin account. Our web service logs in using a limited account. It needs to ...
Cruncher's user avatar
  • 223
12 votes
1 answer
3k views

Permissions in triggers when using cross database certificates

I use cross database certificates (as explained by Erland Sommarskog) to control access to a certain database in my environment ( SQL Server 2008 R2 ). I have stored procedures in database A that ...
Dave.Gugg's user avatar
  • 294
305 votes
10 answers
994k views

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...
pedrosanta's user avatar
  • 3,363
27 votes
8 answers
103k 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 ...
Michael J Swart's user avatar
18 votes
3 answers
125k views

"ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
Suhail Gupta's user avatar
6 votes
1 answer
5k views

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

I have created a few Certificates (via CREATE CERTIFICATE) and Asymmetric Keys (via CREATE ASYMMETRIC KEY) and used them to sign and counter-sign various SQL modules: Stored Procedures User-Defined ...
Solomon Rutzky's user avatar
27 votes
4 answers
63k views

Find objects linked to a PostgreSQL role

Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9). I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ...
Nicolas Payart's user avatar
14 votes
1 answer
21k views

Access view based on table in another database without account in that other database

I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account ...
tom's user avatar
  • 141
7 votes
4 answers
26k views

Export all MySQL users

I need export all of a MySQL database users (including permissions) for a migration. There is over 5,000 users, what is the best way to do this? Should I just dump the mysql.user table?
Justin's user avatar
  • 439
6 votes
2 answers
8k views

administrator permissions on a SSAS database

I had a third party company coming over and building ssas databases to produce reports. They did, but now the DBA wants to have a look but I haven't got permissions, to even see those databases, as ...
Marcello Miorelli's user avatar
6 votes
1 answer
4k views

SQL Server: hierarchy of permissions for schema?

We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is ...
jrara's user avatar
  • 5,333
5 votes
2 answers
3k views

Can't use msdb.dbo.sp_send_dbmail when in service broker - executes as guest?

I have a procedure TheNotificationProcedure that makes a cross-database call to msdb.dbo.sp_send_dbmail. It gets invoked (indirectly) from a service broker queue: CREATE QUEUE [Blah].[TheQueue] WITH ...
Nathan's user avatar
  • 151
5 votes
3 answers
1k views

Giving Special Permissions to a Stored Procedure in SQL Server

I have a restricted database (that contains confidential data) called database B with only a couple of users with access to it. However, I need some select data from a couple of tables within B to be ...
BeginnerDBA's user avatar
4 votes
1 answer
11k views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
Ivan De Sousa Paz's user avatar
3 votes
2 answers
6k views

Why can a new user select from any table?

When I create a user in Postgres, create user test with password 'test'; The user ends up being able to connect to every database in the instance. The user can also select from any table: # psql -...
Andomar's user avatar
  • 3,495
1 vote
2 answers
2k views

how to grant permissions to all the synonyms inside a view?

I have to grant permissions to a view. dbo.my_view, however, this view joins many tables, and other views and synonyms. using the following script I can find all all the synonyms in my server: ...
Marcello Miorelli's user avatar
1 vote
2 answers
15k views

How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement?

MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine... I am trying to add another SQL Server Agent Job to copy the .bak files to remote (non-windows, i.e. ...
Gennady Vanin Геннадий Ванин's user avatar
68 votes
3 answers
101k views

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of ...
andreas-h's user avatar
  • 813
33 votes
5 answers
186k views

Permission denied for relation <table>

I have ran the following SQL in psql: CREATE USER bspu LOGIN; CREATE DATABASE bsp OWNER bspu; GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu; \c bsp CREATE TABLE users ( id SERIAL PRIMARY KEY, ...
if __name__ is None's user avatar
12 votes
2 answers
27k views

Explicitly granting permissions to update the sequence for a serial column necessary?

Recently I did create a table as a superuser including a serial id column, e.g., create table my_table ( id serial primary key, data integer ); As I wanted my non-superuser user to have ...
moooeeeep's user avatar
  • 223
11 votes
4 answers
80k views

script to show all the permissions for a table

inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update. when I run this script select * from sys.objects where name = 'spGen2_tblIE_SchedProcess_Update' Inside the same ...
Marcello Miorelli's user avatar
8 votes
1 answer
199 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute stored procedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
BenVlodgi's user avatar
  • 265
7 votes
2 answers
9k views

Effective SQL Server permissions when user is in several AD groups

Using Window Authentication via Active Directory Groups, if an Active Directory user exists in two different Active Directory Groups with different permissions to a database, which Active Directory ...
Phil's user avatar
  • 73
6 votes
3 answers
7k views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
Ivan Sopov's user avatar
5 votes
1 answer
8k views

How can I grant permissions on tables created in the future to members of a particular role?

I would like to create a role which will be granted to several individual users. This role should have full permissions on all tables, sequences, functions, etc. in a specified schema. When new tables,...
speedyturkey's user avatar
4 votes
1 answer
1k views

Execute Permissions for a Store Procedure that creates databases

I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to ...
JPVoogt's user avatar
  • 223
3 votes
6 answers
8k views

How do I identify all super users in SQL Server?

For a security audit,I need to identify all super users and log every activity performed. Best approach for this? By "super user" I mean sysadmin, serveradmin, securityadmin, processadmin, and ...
Ikenna's user avatar
  • 33
3 votes
1 answer
3k views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
Marcello Miorelli's user avatar
2 votes
2 answers
16k views

Issues with module signing and SSIS catalog internal procedures

I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user. I found this article which ...
Mansfield's user avatar
  • 1,021
2 votes
1 answer
2k views

How to script out the grant view on login permission?

I create the following login without any permissions. USE [master] GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe') CREATE LOGIN [Radhe] WITH PASSWORD=N'...
Marcello Miorelli's user avatar
50 votes
2 answers
202k views

SQL Server - granting permissions to an entire schema vs. object?

I'm very green when it comes to the world of database permissions management in SQL Sever. Let's keep an example simple. Say account 'admin' is the owner of schemas A, B, and C. There another ...
user45867's user avatar
  • 1,683
38 votes
2 answers
115k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
Celeritas's user avatar
  • 883
28 votes
6 answers
39k views

How can I create readonly user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I ...
milosgajdos's user avatar
24 votes
5 answers
103k views

What permissions are necessary for truncating a table?

I have a SQL account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION [...
Mansfield's user avatar
  • 1,021
24 votes
1 answer
83k views

Grant access to all tables of a database

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. ...
d33tah's user avatar
  • 389
21 votes
2 answers
27k views

Broken NTFS Permissions for MSSQL$SQLEXPRESS

I have been "securing" our database server. To do this I removed some permissions from the folders that the SQL Express install created. D:\SQL D:\SQL\Data D:\SQL\Backup etc.. I kept the ...
Mat Fergusson's user avatar
16 votes
1 answer
5k views

How do I find out *why* a user has certain effective permissions?

I know that I can query effective permissions by using sys.fn_my_permissions: USE myDatabase; SELECT * FROM fn_my_permissions('dbo.myTable', 'OBJECT') entity_name | subentity_name | ...
Heinzi's user avatar
  • 3,145
16 votes
4 answers
19k 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 ...
jmoreno's user avatar
  • 1,063
14 votes
1 answer
17k views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
user avatar
14 votes
2 answers
39k views

Allow user to do anything within his own schema but not create or drop the schema itself

I have created a schema in SQL Azure and granted the following permissions to a database role: CREATE ROLE myrole AUTHORIZATION dbo; EXEC sp_addrolemember 'myrole', 'myuser'; CREATE SCHEMA myschema ...
Imran Qadir Baksh - Baloch's user avatar
13 votes
1 answer
3k views

Why is a new user able to create tables in PostgreSQL?

I followed two tutorials to create a DB with: a fully privileged user link a read-only user link I then got a tip from CJ Estel's tutorial stating that "you may have inherited the ability to create ...
Jesvin Jose's user avatar
13 votes
1 answer
14k views

Mapping of multiple server logins to one database user

This seems to be a stupid question but despite some research I was unable to find any information regarding this only (possibly due to using the wrong terminology). Is it possible to log multiple ...
leepfrog's user avatar
  • 253