Thank this author by sharing:
By Andy Warren,
I think we all should have some type of base philosophy about security that
keeps on us track during busy weeks. Some of us are lazy about it, some haven't
thought about it, some would protect the data so well no one could use it! A
full discussion of security could easily fill a book, so let's narrow it down a
bit for this discussion by looking at what rights you give to non sysdamin
users. These comments apply to production and usually QA servers, not
I start with a pretty simple approach; you're either a sysadmin or you're
nobody! I like the concept of junior/assistant DBA's to handle the workload, but
in practice they don't get the job unless I trust them. So even though we have a
number of fixed server roles (listed below) I don't use them. I'm not suggesting
they be removed from the product as they may be useful in some scenarios, but in
my career I've never had a reason to use them. I don't see any point in giving
someone just processadmin for example, how often is there a need for a person
you only trust to kill spids that run wild? The one temporary exception at the
server level is to let someone temporarily have permissions to run a trace
(ALTER TRACE) which was added in SQL 2005, prior to that they had to be a
Equally, I don't assign anyone to any of the fixed roles on a permanent
basis. There may be times when I temporarily grant someone deeper access to a
single database and in most cases I'll put them in db_owner, supplementing that
with a warning to them to not screw things up! In particular I don't use
db_datareader/db_datawriter, preferring to explicitly grant permissions in all
cases and almost always preferring to gate access using stored procedures.
Within the database I create one or two roles per application depending on
how it's being built. For example, for most web applications everything will run
under a single account, so I'll create a role called MyWebApp, add the
login/user to that role, and then grant the appropriate access. Having the role
allows me to add - if needed - a developer so that they can debug using the
exact same permissions. In some cases I'll set up a secondary role called
MyWebAppRO, the RO indicating it's a read only role and I use it for any type of
dynamic search that might have a sql injection vulnerability.
As far as logins, I prefer to use Windows groups whenever possible, starting
with a base DBA group that I assign to the SQL sysadmin role. Using groups
reduces the time I spend administering and of course using NT authentication
lets me leverage the strengths of AD; requiring strong passwords, account
lockout, etc. For applications I prefer that the connection be done using a
single service account (unless we need a separate one that will be for read
only) and I load these individually, not as a group. Ideally that service
account is used only on an application server/web server. If a sql login is used
- and this is common for desktop client/server apps - I also set up a separate
login per application, and try to make sure that the developers have been very
careful about how they store/retrieve the password for the account.
Is that overkill? Am I missing out on the possibilities that the built in
roles offer? Or do you agree with my philosophy?
Visit my blog at http://blogs.sqlservercentral.com/andy_warren/default.aspx
Options and impacts when creating, altering and dropping an index
Alter Database with variable
Today Steve Jones looks at the CREATE OR ALTER syntax, added in SQL Server 2016 SP1.
Learn why ALTER DATABASE should be preferred over Detach/Attach for moving database files on the sam...
Use this select statement to create a list of ALTER SCHEMA statements for all stored procedures in a...