Tag Info

Hot answers tagged

80

It really depends on whether the developer has any support responsibilities. If they are on the hook for third line support then they will probably need to look at the production database to do this. Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there. For most development purposes, mirrors or ...


73

No. Developers should not have access to production database systems for the following reasons: Availability and Performance: Having read-only rights to a database is not harmless. A poorly written query can: Lock tables, blocking other critical processes. Trash your data cache, forcing other processes to re-read data from disk. Tax your storage layer, ...


48

Performance would be a BIG reason. Just because they can't change the data doesn't mean they can't affect the server. A poorly written query could bring the production environment to its knees, and potentially cause other issues (like tempdb overflows): SELECT * FROM BigTable A, OtherBigTable B ORDER BY Somecolumn That's a recipe for disaster. Notice ...


15

The principle is "least privilege" and "need to know": do developers pass this test?Especially when Auditors or Sarbannes-Oxley come knocking. Then, my next assumption: developers are stupid. So if they do need say for 3rd line support, who then needs it? Web monkeys typically don't but database types yes if they are expected to support it. Then, is access ...


13

Making SA the owner of a database actually simplifies and/or solves a number of things, but can have some security implications. In particular, remember that if SA is the owner of a database, then dbo = 'SA'. This means that, among other things, any procedures in the [dbo] schema (which is the default) that have "EXECUTE As Owner" in them, are actually ...


11

Security: There might be sensitive information that is sanitized when they make it available to developers. Paranoia: Some might think you could still mess up data with just select access. Performance: A query takes some resources to perform, and you can't tell me your developers are perfect when they write code.


11

Form Query Analyzer or Management Studio, you can issue the following command: ALTER DATABASE database-name SET READ_ONLY As mentioned here, you need the alter database rights and you must set the database to single mode. Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive access to the database. USE master; ...


10

On an usual 24/7 OLTP environment a normal developer shouldn't be allowed in production. Period! If, from time to time, a particular reason appears, than permissions could be granted upon request. But on a usual basis no. I've seen many reasons for this: SELECT * from a big table leading to: performance issues (cartesian products); blocking issues that ...


10

You could create a stored procedure that runs the job. You can use with execute as owner to run the stored procedure as the database owner. That way, the users themselves don't need permissons on sp_start_job. create procedure dbo.DoYourJob with execute as owner as exec sp_start_job @job_name = 'YourJob' Grant execute rights on DoYourJob to allow people ...


10

Nothing built-in. You have two options though: Use common_schema's sql_show_grants view. For example, you can query: SELECT sql_grants FROM common_schema.sql_show_grants; Or you can query for particular users, for example: SELECT sql_grants FROM common_schema.sql_show_grants WHERE user='app'; Disclaimer: I am author of this tool. Use Percona ...


10

The syntax you're using is attempting to change ownership of the object 'DestDB' within whatever database context you're currently in. You need to specify that you're changing database ownership by using the class definition: ALTER AUTHORIZATION ON DATABASE::[DestDB] TO [sa]


9

I think the answer is, like with many things IT, "it depends". A massive ERP database with lots of sensitive company and customer information? Probably not (both for security and performance reasons). A departmental 5 MB database with an Access front-end that tracks contributions to the donut and pizza funds? Not going to make a whole lot of difference, at ...


9

This doesn't look like a permissions issue at all, but rather a bug in the UI. If you are using Management Studio Express, then you should consider two options: Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well. Many of the bugs in the 2008/2008 R2 version of ...


8

SQL Server 2000, 2005, 2008 has this capability GRANT { ALL [ PRIVILEGES ] } | permission [ ( column [ ,...n ] ) ] [ ,...n ] [ ON [ class :: ] securable ] TO principal [ ,...n ] [ WITH GRANT OPTION ] [ AS principal ] ADDED In SQL Server 2005, 2008 it is possible to encrypt a column of data by using symmetric encryption, see ...


8

If it's not too late, one compromise option that I've seen work well is rather than upgrade the permissions or replacing the developers' existing accounts, create a separate account that is only used when they need the elevated permissions. So normally they work under individual "restricted" accounts (which I use loosely because these restricted accounts ...


8

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.: GRANT SELECT ON dbo.Table_Valued_Function TO [testuser]; So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much ...


8

This account is a service SID. The full name is NT SERVICE\MSSQL$SQLEXPRESS.


8

The best place to look for this information is in books online. The article on TRUNCATE TABLE here indicates: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. ...


7

I work as a developer for a very large company. All of our developers that will be doing any sort of support (basically all of them) have access to relevant production databases. I can only speak for my specific team, but I will tell you why we have access. We need to realtime access to keep an eye on our daily processing. (While we have a dashboard, we ...


7

For this question to be asked one must presume that they currently do not have access. If one's organization is developing software and this is for troubleshooting a customer issue and the customer supplies a copy of their database, then 'yes'. Otherwise I would advocate keeping developers out of production and have alternate enivironments created for ...


7

PUBLIC has access to the database by default, but it can't access the data. You can REVOKE the PUBLIC: REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; If you want this setting for all future databases, revoke CONNECT on the template1 database (default template database for creating a new database): REVOKE CONNECT ON DATABASE template1 FROM PUBLIC; ...


7

Start the SQL Server service in single-user mode (by using the -m startup parameter). When you do this, all members of the local administrators group will have sysadmin rights. Provided you are a member of that group, you should be able to do whatever sysadmin actions you need. Take a look at Database Engine Service Startup Options for documentation. ...


7

It depends, and I'm afraid Martin give you only part of the answer. The credential used to access the file is different whether you logged in with SQL login or a Windows login. For SQL logins (ie. user and password based connection string) the credential used is either the SQL server process account of the login proxy credential account, if one is set up: ...


7

Access to databases in SQL Server is managed mainly through two principals: Logins - Security principal that allows access to the instance. Users - Security principal linked to a login that allows access to a specific database. By default, SQL Server follows the practice of least privilege, meaing that when you create these principals (further ...


6

I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server. The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions. ...


6

Quick answer: For developers, you can GRANT CONTROL on that schema. Background: To see what can be GRANTed: GRANT Schema Permissions To see what each permission means: Permissions Naming Conventions The intersection of these two give the schema permission meanings: CONTROL implies the rest and is the highest permissions of any securable SELECT, ...


6

While not a concrete answer, I find no configuration option or setting to override this behavior. It seems to be baked in. Everywhere Detaching and Attaching Databases is mentioned in the documentation points back to the Securing Data and Log Files page you referred to above. The scenario on this page makes it pretty clear what is going on, and why the ...


6

Huh? Is the data sensitive? Are the programmers part of a core trusted team or some offshore team? What is the scale of the data being queried upon in terms of impacting performance? What is the scale of the project or dollars involved? How critical is the uptime? Answer those before asking this question and you will get a better answer. The reason being ...


6

I believe I have found the authoritative answer, helped greatly by the table provided in this article: http://msdn.microsoft.com/en-us/library/ms178569.aspx. It would seem CREATE DATABASE is indeed the ANSI SQL compliant permission, while CREATE ANY DATABASE is an MSSQL proprietary server permission. However, the two are not the same. In fact, there is a ...


6

None. sp_executesql executes SQL, under exactly the same context and privileges as the original caller would execute the same SQL. There are many, many, cases when dynamic SQL is unavoidable.



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