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

learn more… | top users | synonyms (2)

6
votes
2answers
4k views

Does the Sql Server Agent account need sysadmin permission?

I ran Brent Ozar's sp_blitz script, and one of the things it's complaining about is that my SQL Server Agent account has sysadmin permission. I removed the sysadmin permission, but then the agent ...
6
votes
1answer
2k 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 ...
6
votes
1answer
428 views

Steal ownership on database from user that no longer exists

We have a few SQL Server 2008 R2 databases where its member domain was changed (i.e. it was connected to DOMAINA and is now on DOMAINB). We have three databases where the owner of the database is ...
6
votes
1answer
2k views

Permissions to see and script objects, but not alter them?

I am a software developer. I have a production database that I need full ddl read access to, but not any ddl write or execute access. Is there a script I can send my DBA that will grant me full ddl ...
6
votes
1answer
176 views

Principle of least privilege for sql server agent jobs

I've just read this article which advocates using the sa account for ownership of sql server agent jobs. http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account He has a couple ...
6
votes
3answers
4k views

db_owner unable to drop database - Error 615, SQL Server

I have a SQL Server 2012 database running on amazon EC2. I've created a user to be able to create, edit and drop databases. I gave the new user with dbcreator server role. My user can connect ...
6
votes
1answer
22k views

Cannot output MySQL data to file

I am trying to output the data from an MySQL table to a file but getting permission errors: $ pwd /home/dotancohen $ mkdir in $ chmod 777 in/ $ mysql -ugs -p mysql> USE someDatabase; mysql> ...
6
votes
1answer
94 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute storedprocedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
6
votes
1answer
11k views

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

I encounter the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. when I was trying these commands USE ASPState GO GRANT ...
6
votes
2answers
2k views

MySQL Permissions Necessary To Reset Password

On my team there is a desire for some users to periodically update their MySQL password. Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm ...
6
votes
2answers
817 views

How to retrieve granted privileges without access to the database “mysql”?

As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?
6
votes
2answers
1k views

Is there a way to setup a user to run SPs, but not access the underlying data tables?

We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not ...
6
votes
1answer
531 views

Is there a way to DENY CRUD operations on connections from linked server?

I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), ...
6
votes
3answers
67 views

Can't get access to other database

I am working on a project for my university. They gave me a login username and password for MySQL database they've setup on the server. With that username and password, I'm able to login to the ...
6
votes
2answers
5k views

Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)?

Using MS SQL Server 2012 in Microsoft Windows Server 2008. I am somewhat confused by the New Account Types Available with Windows 7 and Windows Server 2008 R2 virtual Windows accounts [NT SERVICE]\&...
6
votes
1answer
26k views

MySQL user permission on stored procedure

I've created a simple stored procedure: mysql> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected, 1 ...
5
votes
8answers
1k views

Granting SA privileges for Developers on the development box

In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still ...
5
votes
1answer
5k 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 ...
5
votes
1answer
4k views

Attaching a database in SSMS gives error

I am accessing SQL Server using SSMS, and a user with adequate permissions. When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), ...
5
votes
2answers
26k views

Granting Execute Permissions on Stored Procedures, Functions and Views

I am using a user which has read permission only. It basically has permission to Connect Execute However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
5
votes
2answers
1k views

What permission is needed to set Database read-only?

I am dbowner of a database which I want to set read-only. (through sql management studio). When I try that I get a permission error (297) Which role is needed to perform that action?
5
votes
3answers
2k views

Possible to detach a database from SQL Server while retaining file permissions?

When a user detaches a database from MS SQL Server, it sets the owner of the data and log files to that user. It also removes all non-owner permissions from the files. This is partially explained at ...
5
votes
2answers
4k views

What is the difference between ROLE and PRIVILEGE for Oracle?

Is role a collection of certain privileges?
5
votes
6answers
15k 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 (...
5
votes
2answers
185 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 ...
5
votes
2answers
9k views

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

I am trying to grant privileges for a user to a new database mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword'; I get the following error ERROR 1054 ...
5
votes
3answers
18k views

Users cannot view tables in non-default schema in SSMS

I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has ...
5
votes
1answer
12k views

How to grant a user UNSAFE ASSEMBLY permission

How can we assign a user , e.g.: the database owner (DBO) the UNSAFE ASSEMBLY permission? I'm asking it to enable a user to create an assembly and don't know the SA password, so EXEC sp_changedbowner ...
5
votes
2answers
148 views

Will a SP which writes records fail if accessed by a db_datareader role?

I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that. Recently there ...
5
votes
3answers
3k views

BUILTIN\Administrators accidently removed

Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. ...
5
votes
1answer
3k 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 ...
5
votes
1answer
142 views

How to run SQL services on NT SERVICE\MSSQLSERVER account if it is running earlier on LocalSystem

Recently just for experimental puprose, I have relocated the default (C:...) data\log file paths in server settings to new location (G:\DBA). Then I've moved the previously existing db's data\log ...
5
votes
1answer
560 views

Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently: SET ANSI_NULLS { ON | OFF } SET QUOTED_IDENTIFIER { ON | OFF } SET ANSI_PADDING { ON | OFF } ...
5
votes
2answers
18k views

Can't start MySQL server because of wrong file permissions?

I'm trying to start the server and then fix the root user by $ mysql -u root mysql $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root'; $mysql> FLUSH PRIVILEGES; But ...
5
votes
1answer
3k views

minimum privileges required for a user

I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server. There are many other things to do to harden the database but a good place to start is user ...
5
votes
1answer
5k views

select permission was denied on the object 'sysjobs'

The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?
5
votes
1answer
105 views

Monitor table permission changes

I need a solution to monitor table permission changes: how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?
5
votes
1answer
866 views

SQL Server Security Hierarchy

I have been working as a developer with SQL Server for years without asking the question, "what is dbo", a shame. Recently I have been studying the security model of SQL Server 2008. I have been ...
5
votes
1answer
286 views

Restrict Postgres roles by IP address outside of the pg_hba.conf file

I am currently adding Postgres support to a deployment tool that currently only supports MySQL. This tool dynamically provisions and configures a database, among other things. In MySQL, we can ...
5
votes
2answers
40k views

How to grant all privileges on all tables in a schema to a user in IBM DB2?

At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move ...
5
votes
3answers
3k views

Do roles have an owner in oracle?

Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles ...
5
votes
2answers
2k views

Role db_ddladmin not working properly on SQL Server 2008

I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner. I thought I could do it with the db_ddladmin role, ...
5
votes
1answer
50 views

What permissions are required to return rows from information_schema.schemata?

In postgres (I'm on 9.3.4) what permissions are required to return rows for select * from information_schema.schemata The 9.3 documentation says "are owned by a currently enabled role". Does that ...
5
votes
1answer
57 views

Checking SQL AlwaysOn backup target with fn_hadr_backup_is_preferred_replica, different answers with different permissions

I've implemented AlwaysON backup with Ola's script, those are running fine on designated server configured for backup. I would like to automatically check backups using sql-scripts fired from a ...
5
votes
1answer
61 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 ...
5
votes
2answers
1k views

Grant users privilege to use QUERYTRACEON 9481

Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120. In my situation, "degraded" ...
5
votes
1answer
411 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
4
votes
1answer
14k views

SQL Server: grant select access to a user in a view and not in its tables

I have a SQL Server 2012 instance with a few databases. In one of them I created a view, that selects tables in more than a database. I want a user to be able to select that view, but it must not ...
4
votes
3answers
6k views

Permission to view execution report in SSIS Catalog

We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin? This is for production environment and we don't want ...
4
votes
3answers
15k views

When creating views, why do users need direct object permissions if they already have the same permissions via a role?

I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this. I have a schema with a table in it. I have granted select on that table to a role. grant ...