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

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 ...


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 ...


12

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. ...


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; ...


11

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


11

It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own. EDIT: For clarity on the three options presented by explicitly mentioning the SQLAgentOperatorRole as an option and by adding some explanation on the third solution. (1) If the user is allowed to manage the execution of all jobs, ...


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

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

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 ...


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

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

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 ...


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

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 ...


7

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, ...


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 ...


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

Generally speaking, adding AD groups to database roles and granting the permissions to the roles gives you some separation of purpose. In that case you would use them as: AD Group = a set of logins that share the same database access needs. Role = a set of permissions that define a set of rights to be shared by logins. The advantage is that the role can ...


7

As per this reference in BOL: 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. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored ...



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