Privileges granted to an account or role through the security mechanism of an operating system, database manager or other system.
63
votes
18answers
8k views
Should developers be able to query production databases?
Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...
9
votes
1answer
329 views
Are there reasons why I should not set my db owner to [sa]?
Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear.
Is there any, good reason or circumstance why I should ...
9
votes
1answer
3k views
CREATE DATABASE vs. CREATE ANY DATABASE permissions
In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions?
I am unable to find an authoritative answer. The best I can infer is that either (a) ...
8
votes
1answer
486 views
What are the security implications of sp_executesql?
By default, in SQL Server, the [public] role has EXECUTE rights on sp_executesql.
However, I've inherited a database server where the previous DBA has revoked the EXECUTE right on sp_executesql.
As ...
7
votes
3answers
254 views
Identify why a user can drop a database
I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user ...
7
votes
2answers
12k views
Grant permissions to run an SQL server job
I have a job on my MSSQL server 2005, that I want to allow any database user to run.
I'm not worried about security, since the input to the job's actual work comes from a database table. Just running ...
7
votes
1answer
868 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 ...
6
votes
8answers
600 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 ...
6
votes
1answer
252 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), ...
5
votes
6answers
421 views
Which database engines will allow me to GRANT/REVOKE on a specific column?
If I have a table with a single column of sensitive data, and I want to grant broad use of the table without exposing that one column, I know that I can create a VIEW that gives them access to all the ...
5
votes
2answers
8k 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
451 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
1answer
141 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 ...
5
votes
2answers
95 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
1answer
119 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
2answers
123 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 ...
5
votes
0answers
43 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
5answers
5k views
User Permissions Messed Up following a Backup -> Restore Operation
I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
4
votes
3answers
3k 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 ...
4
votes
2answers
486 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. ...
4
votes
2answers
165 views
Enumerate drives available to SQL Server
I'm trying to work out a way of listing the drives that a particular instance of SQL Server is "aware" of, and understand the process.
I can run EXEC master..xp_fixeddrives which gives me a list of ...
4
votes
2answers
275 views
Need help figuring out correct permissions for SQL Server Users
In most of my databases I create, I need to give permissions to do the following:
SELECT/INSERT/UPDATE/DELETE on all tables in the database
Execute all (user) stored procedures in the database
...
4
votes
1answer
2k 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 ...
4
votes
2answers
115 views
Track grant permission on tables
I'd like to see if there are any changes on the permissions granted for a specific table.
For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices.
If ...
4
votes
1answer
348 views
Is the PostgreSQL 'connection limit' shared across all users in a role?
I have created a role that many users can share:
> create role foo_read_only connection limit 5;
I have then assigned permissions to that role:
> grant select on table1 to foo_read_only;
...
4
votes
1answer
409 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 ...
4
votes
1answer
113 views
Managing Access for DBA and IT Support
We are currently in the process of moving our development, test and live databases on to separate servers and instances and I am looking at how best to manage access for DBA tasks and also IT Support ...
4
votes
1answer
64 views
Is it possible to encapsulate a table in MySQL?
I am new to Databases. I want the following:
UPDATEs in a database to happen only through a stored procedure
The user does not have GRANT UPDATE
The user has GRANT EXECUTE ON PROCEDURE
I tried it ...
4
votes
1answer
417 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 | ...
4
votes
1answer
3k views
PostgreSQL: permission denied for relation
I'm a bit confused about setting permissions in PostgreSQL.
I have these roles:
List of roles
Role name | Attributes | Member of
...
4
votes
0answers
120 views
SSRS appears to be ignoring Permissions set using Report Manager
I have setup SSRS on SQL Server 2008 in native mode.
As an administrator I can login to report manager, upload reports and run them, and also use the Web Service URL to generate reports.
I have also ...
3
votes
2answers
174 views
Get the windows account which runs the query?
I'm using sql server 2008 r2.
I logged in to ssms which in turn connects to a remote sql server machine.
im writing a query which writes a file.
I need to know - which windows permissions I should ...
3
votes
1answer
662 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 ...
3
votes
2answers
343 views
User can alter procedures without permission
I have a sql server user that our application is connecting as. The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use.
...
3
votes
1answer
325 views
Does this mean the 'public' has full select, insert, update, and delete access?
I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View. But no matter what I try when I login as this new user I can select from any table. I ran this query: ...
3
votes
2answers
126 views
SQL 2005 Permission Precedence
Very simple question -
I have two database roles (Basic, Admin). Say I explicitly deny the Basic role from deleting from table A and grant that to the Admin role. If I am a user who is in both ...
3
votes
1answer
4k views
Receiving “The SELECT permission was denied on the object” even though it's been granted
I'm a programmer, not a dba... I know just enough to be dangerous.
I've inherited a database with a legacy user that is a db_owner for the database. We can't adjust this user's permission for ...
3
votes
1answer
1k 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?
3
votes
1answer
27 views
How should permissions for anonymous users be modelled?
I'm designing a web application. There will be users who log into the site, but also anonymous, non-authenticated users, i.e. any member of the public who accesses the site. Users will be assigned to ...
3
votes
1answer
600 views
How do you gain access to embedded MySQL database via localhost command line?
We run an application (Windows 2003 server) and would like to have database connectivity -- contacted the company and they do not have any documentation on how to do so. A quick glance at the ...
3
votes
2answers
882 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, ...
3
votes
2answers
1k views
Select permission inside stored procedure?
I have granted a user execute permission for a stored procedure through SSMS, but when he tries to execute it, he gets the error:
The SELECT permission was denied on the object '[table name]', ...
3
votes
2answers
950 views
Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?
I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying:
SQLServer login APPUSER is member of ...
3
votes
1answer
192 views
SQl Server 2008 R2 - sa can login but not access database
Up until this morning I had no problems accessing our database.
This morning, something has changed and I can no longer access our database (called EMS_Main) using the sa login. I can log into the ...
3
votes
1answer
312 views
DENY EXECUTE on one schema object hides other objects in same schema
I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give ...
3
votes
1answer
113 views
Permissions Dropped after Package Failed
I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live ...
3
votes
2answers
622 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 ...
3
votes
0answers
161 views
Deny view definition at DB level and symmetric keys MS SQL Server 2005
I don't want a restricted database role to be able to view table and stored procedure definitions and code.
So I do
deny view definition to some_db_role
It does appear though that this is not ...
2
votes
2answers
1k views
SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network
We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database ...
2
votes
2answers
778 views
Set permissions on a view
I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to ...
