Tips
Accessing the Windows File System from SQL Server
Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory. What are some approaches to do so? I need some flexibility to capture the file names and general information for subsequent processing. What are the native SQL Server options as well as the custom options that are available?
ASP.NET Security for SQL Server
In all of my ASP applications I need to have at least basic security, how can I do so in simple manner? The reality is that in the majority of my applications I need membership, profiles, role management and personalization, are any applications available directly from Microsoft to handle this need? If so, where can I find them? Do they integrate with the .NET 2.0 framework? If so, how do you set this up? Where can I find more information?
Assigning DBA Rights in SQL Server
As a DBA you have the responsibility to ensure the security for the application and users related to the SQL Servers that you manage. But what are the security principals that you follow for the administrators? Are there any hard and fast rules that must be followed? What concerns should be taken into consideration when the rights are assigned to DBAs? How is this any different for a large organization versus a small team where many of the team members wear numerous hats (DBA, Developer, Network Admin, System Admin, etc.)?
Auditing Failed Logins in SQL Server
An auditor has taken a look at our SQL Servers and has told us that we need to audit login failures to the SQL Servers themselves. How do we do this?
Auditing SQL Server Password Age
In Active Directory our administrators are able to audit password age to see when accounts last had their password changed. Most of our service accounts are set up to not expire passwords because we change them less frequently than we require users to do so. As a result, the administrators are required to run checks to see how old passwords are for these accounts. We've been asked to do the same for our SQL Server logins. How do I do this? Check out the solution for this tip.
Auditing SQL Server Permissions and Roles for the Server
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?
Auditing SQL Server User and Role Permissions for Databases
I have been tasked with auditing security on my SQL Server. I'm trying to audit permissions within the database itself. This needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. In this tip we look at the tables and/or views to use and the best way to extract the information.
Auditing Windows Groups from SQL Server
Managing database security should be part of every DBAs job. With SQL Server you have the ability to use either SQL Server and Windows or Windows only security. The advantage of using Windows security for your SQL Servers is that you can take advantage of the Windows security model and security policies. The disadvantage is that the specific people within these groups is masked at the SQL Server level. In your logins you can see the groups that have access, but you have no idea what users are in what groups. So how can you get this information from within SQL Server?
Automate collection and saving of failed logins for SQL Server
I re-cycle my SQL Server log every night using sp_cycle_errorlog. However, before I do, I would like to capture all of the failed logins recorded. I have auditing turned on for failed logins, but I want to make sure that I capture those events into a table so I can report on. How can I do this?
Breaking ownership chaining within a schema in SQL Server
I have several objects, all in the same schema. Because of this, ownership chaining is working, as described in a previous tip. However, I do not want ownership chaining to be on, but I need the objects to remain in the same schema. How can I do this?
Cannot make a role a member of itself error in SQL Server
I am attempting to nest roles in creating my database security model, but I'm getting an error when I use sp_addrolemember. The error is this: Msg 15413, Level 11, State 1, Procedure sp_addrolemember, Line 92 Cannot make a role a member of itself. I'm not making a role a member of itself, so I don't understand this error. In this tip we will look at what causes this error.
Correct the SQL Server Authentication Mode in the Windows Registry
I have a SQL Server 2005 instance with Mixed Security Mode, BUILTIN\Administrators rights revoked and I have not set up a Dedicated Administrator Connection (DAC) in the SQL Server 2005 Surface Area Configuration tool. I have tasked my Junior DBA with setting up sysadmin server role rights for the Active Directory group that contains all the domain logins for your DBAs and then to change the security mode to Windows Authentication Mode. Unfortunately, the Junior DBA changes the security mode first and now you find yourself locked out of your own SQL instance. What do you do to regain access to the SQL Server 2005 instance?
Creating a SQL Server proxy account to run xp_cmdshell
In my previous tip, I showed how you could dynamically execute a SQL Server Integration Services (SSIS) package from T/SQL. What I did not discuss were the processes by which you could reduce the security risk that enabling xp_cmdshell produces, and in doing so what the ramifications are on security inside the database being accessed via SSIS. It's time now to take care of those topics!
Database level permissions for SQL Server 2005 and 2008
I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the database level. What are they and what do they give rights to perform?
Different ways to secure the SQL Server SA Login
What are the different ways to secure the SA login? Everyone is aware of the SA login and its administrator rights, so it is very risky if some unwanted users try to use the SA account or hack the SA account. It is also not advisable to use the SA account in any application or by users. In this tip we cover a few ways to reduce the risk.
Dynamic SQL and Ownership Chaining in SQL Server
I have tried to use ownership chaining like in a previous tip, but it doesn't seem to be working. In my case, I'm building a SQL string (dynamic SQL) to execute within the stored procedure and then executing using either EXEC or EXEC sp_executesql. However, I keep getting an access denied error when the string executes. Both objects are located in the same schema and neither object as an explicit owner defined. What am I doing wrong?
Enabling xp_cmdshell in SQL Server 2005
Sometimes there may be a need to run some external processing from within SQL Server. To make this task easy Microsoft has a built-in extended stored procedure called xp_cmdshell. With SQL 2005 the use of xp_cmdshell is turned off by default and needs to be enabled if you want to use it for any processing on your server.
Extend the REVERT statement using the WITH COOKIE clause in SQL Server
In a previous tip on Switching Execution Context using the REVERT clause, you have seen how to switch execution context to a more privileged user and revert back to the original context of the caller within a programmable object. The REVERT clause accepts a parameter WITH COOKIE to provide an even more granular option. How do we use it?
Filtering Columns in SQL Server Using Views
I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately, so that not everyone can select the data? In this tip we show how this can be done using views.
Filtering SQL Server Columns Using Column Level Permissions
I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data? In a previous tip, Filtering Columns in SQL Server Using Views we looked at using Views. In this tip we cover how this can be done with column-level permissions.
Get Alerts for Specific SQL Server Login Failed Events
In a recent MSSQLTips.com question, a user asked how they could get an alert whenever a login failed due to the account being locked out. In this tip I provide different options to handle this need.
Get Back into SQL Server After You've Locked Yourself Out
Someone, while locking down the SQL Server, removed the permissions by which the DBAs came in and administered the server. As a result, we cannot get back into SQL Server. How can we restore our access to SQL Server? Check out this tip to find out.
Giving and removing permissions in SQL Server
Often times when auditing SQL Servers I notice that most environments use the default database roles to grant access to users. These default roles such as db_owner, db_datareader and db_datawriter are great, but often times they give the users a lot more access then they really need. Giving permissions and removing permissions is not that hard to do within SQL Server, it just takes some time to determine what permissions should be applied.
Grant limited permissions to create SQL Server views in another schema Part 1
I have users who need to be able to create their own views for reporting in a database. However, if I give them the CREATE VIEW permission, they are still getting a permission error when creating the view. I want them to create all the views in a given schema, which matches that of the tables they are building views on. How do I solve this issue?
Grant limited permissions to create SQL Server views in another schema Part 2
In a previous tip, Granting limited permissions to create views in another schema in SQL Server, I showed how to use the CREATE VIEW permission and the ALTER SCHEMA permission to allow users to create new views using the same schema as the tables. However, I have found that the users can alter and drop tables in this schema which is beyond what I want them to do. A DENY CREATE TABLE does not work, so what can I do to prevent them from touching the tables in this way? In this tip I will show you how you can use a DDL trigger to prevent this unneeded access.
Granting permission with the EXECUTE AS command in SQL Server
One area that is often under utilized in SQL Server is issuing granular security rights at both a login and user level. It is often easier to just give someone more rights then they usually need to perform the task at hand versus trying to figure out exactly what rights they need and then issuing just those rights. This is often true where server level roles, such as sysadmin, are granted or database roles, such as db_owner, are granted. By giving logins and users these rights the database user is pretty much guaranteed to have the necessary access that is needed, but granting additional rights could put your data at risk. So what other options are there?
Hiding instances of SQL Server 2005
One of the nice things about SQL Server 2005 is that it is pretty easy to find what machines have SQL Server installed. If you open up SQL Server Management Studio and browse for servers or browse for instances of SQL Server with some other utility it is pretty easy to find all of the installed instances on your network. Is there any way to not have the name of your SQL Servers broadcasted so other people can't find the server without knowing the name of the server and/or instance name?
How do I secure a SQL Server extended stored procedure?
Extended stored procedures in SQL Server 2000 were the only option to extend the capabilities of the relational engine such as access to the file system or to send and receive emails directly in SQL Server. Securing these objects is not straight forward so few organizations have done so leaving SQL Server 2000 in an insecure state.
How to check SQL Server Authentication Mode using T SQL and SSMS
Many times developers want to put logic into their code or SSIS/DTS package to check the SQL Server authentication mode. In this tip we take a look at a couple of ways that this can be done.
How to configure password enforcement options for standard SQL Server logins
I understand that starting with SQL Server 2005, SQL Server could enforce password complexity and password expiration. But I don't understand how that works or what the parameters are for either. I want to be able to explain to an auditor what my SQL Server is enforcing. How do I figure this out?
How to Connect to a SQL Server Named Instance
My network admins have tightened down the network and while we were once able to connect to our named instances via MySQLServer\MyNamedInstance, we're now only able to connect if we specify the port. What happened and what do I need to tell them to fix the issue?
How to connect to SQL Server if you are completely locked out
Suppose you are a DBA responsible for managing all of the SQL Server instances in your environment. For the sake of security you lock down your environment by removing the BUILTIN\Administrators group, disable sa the login, change the port number, removed all users except sa that were part of SYSADMIN server role. Now let's assume during the locked down process, all SYSADMIN accesses has been revoked from your instance. In this case you can successfully connect to the SQL Server instance, but you cannot perform any SYSADMIN tasks. How can you grant SYSADMIN access to manage your SQL Server instance?
How to Create Secure SQL Server Service Accounts
I'm looking to install SQL Server and I know it will require a service account. In this tip we cover what should be done to configure the SQL Server service accounts securely.
How to determine service related privileges for Sql Server service account
How to eliminate SQL Server security issue caused by sp_replwritetovarbin
The February 2009 Microsoft Security Bulletin included an updated Executive Summary (MS09-004) referring to a highlighted vulnerability with Microsoft SQL Server allowing an authenticated user to escalate privileges to those of SYSTEM, thereby taking control of the server. What can be done to protect your SQL Server 2005 instances when it is not able to be patched to SP 3? In the case of the current threat there is a workaround.
How to find out what SQL Server rights have been granted to the Public role
One of the first security issues that forced adoption of SQL 2005 SP3 came in December 2008, when news broke that a vulnerability to Microsoft SQL Server could be exploited via the sp_replwritetovarbin stored procedure. This entire situation started me thinking about all the rights that are granted to the Public server role.
How to Setup Row Level Security for SQL Server
I've been tasked to put together a row-level security model for a database where users are connecting with their Windows user accounts. I'm not sure how to go about this. In this tip we walk through the steps on how to put this together with security mapping tables and database roles.
Identify Local Administrators on a SQL Server box using PowerShell
This tip shows how to identify who the local administrators are on each SQL Server using PowerShell.
Identify SQL Server 2005 Standard Login Settings
Gaining insight into my SQL Server standard and Windows logins has historically been a challenge in terms of determining password changes, failed login attempts, etc. I have noticed that you have experienced the same issue with your tip entitled 'When was the last time the SQL Server sa password changed' and I have noticed some information in the forums on the topic as well. With SQL Server 2005 can I gain any more insight into the SQL Server standard logins part of the equation? I know I can talk to my Network Admin counter parts for some of the Windows related login information. Any and all recommendations would be appreciated.
Identifying SQL Server logins with overlapping server roles
We have all faced this issue: a cursory review of a SQL Server instance we manage appears to have logins assigned to multiple server roles whose rights supercede or overlap one-another. I am specifically referring to the situation where a login is a member of the sysadmins server role and another lesser server role such as dbcreators, bulkadmin, and so forth.
Now, while I do not recommend doling out sysadmin role membership like it is last months leftover Easter candy I also know that it does happen. So is there an easy way to determine situations on your managed SQL Server instances where a login has membership in the sysadmin role and any of the other server-level roles? Certainly. Without opening the Properties window for each ser
Implicit Permissions Due to Ownership Chaining or Scopes in SQL Server
I have audited for permissions on my databases because users seem to be accessing the tables, but I don't see permissions which give them such rights. I've gone through every Windows group that has access to my SQL Server and into the database, but with no success. How are the users accessing these tables?
Implicit Permissions Due to SQL Server Database Roles
I have audited for permissions on my databases because users seem to be accessing the tables, but I don't see permissions which give them such rights. I've gone through every Windows group that has access to my SQL Server and into the database, but with no success. How are the users accessing these tables? In this tip we cover how to determine the access path.
Nesting Database Roles in SQL Server
I know in SQL Server you can nest user-defined database roles within the database, but is that a good idea? Do they work the same as Windows groups when they nest? What about how they interact with the SQL Server provided fixed-database roles? In this tip I answer these questions related to nesting user-defined database roles.
Network communications mechanisms for SQL Server
I am trying to understand how SQL Server communicates on the network, because I'm having to tell my networking team what ports to open up on the firewall for an edge web server to communicate back to the SQL Server on the inside. What do I need to know?
New Security Catalog Views in SQL Server 2005 and SQL 2008
In SQL Server 2000 and below, I relied on sp_helprotect, sp_helprolemember, and sp_helpsrvrolemember to be able to report on all the permissions a given login or user had. However, I'm now supporting SQL Server 2005/2008 and I have read how sp_helprotect is deprecated and does not report on all the permissions. What do I need to use?
Options for cross database access within SQL Server
I need to create a stored procedure that queries data that are not in the current database. What are my options?
Ownership chaining in SQL Server security feature or security risk
I have heard of something called ownership chaining within SQL Server, but I don't know what that is or how it works. I'd like to know if it's a security risk or a security feature and if it's a feature, how do I use it. If it's a risk, I'd like to know how to defend against it.
Potential Security Exploit Using CONTROL SERVER Permissions in SQL Server
I've been tasked with securing my SQL Servers and I need to write scripts to audit them periodically to ensure they are all right. Recently I learned that I need to audit for CONTROL SERVER rights on any SQL Servers I have that are 2005 or above. In this tip we cover what CONTROL SERVER is, how to detect its use and a possible way to exploit this permission.
Replace xp_cmdshell command line use with SQL Server Agent
I need to run something from the command-line, but based on best practices xp_cmdshell has been disabled. The task that needs to run is an internal process that will originate from within SQL Server. Is there a way to do this without using xp_cmdshell?
Retrieving SQL Server Fixed Database Roles for Disaster Recovery
We ran into a case recently where we had the logins and users scripted out on my SQL Server instances, but we didn't have the fixed database roles for a critical database. As a result, our recovery efforts were only partially successful. We ended up trying to figure out what the database role memberships were for that database we recovered but we'd like not to be in that situation again. In this tip we cover how to script these out for recovery.
Retrieving SQL Server Permissions for Disaster Recovery
I'm needing to audit the permissions in my databases, but I want to script them out so I have something to run in case of a recovery situation. I've got the logins, roles, and users handled, but it's the permissions that I want to extract. In this tip we cover the steps on how to do this.
Script to auto generate a security report for your SQL Server instance
A few of my clients have been asking me if I have a template for a security report they can send to their security auditors. I always prepare this report manually for them, using system procedures such as sp_helplogin, sp_helpuser, etc, and then format the data in a Word document. This is a bit of a tedious process that takes me a few hours to complete if the environment is complex (security wise). For that reason, I wanted to create a Stored Procedure that will not only fetch the data, but also provide a formatted report so that my clients will be able to run the report themselves and send it directly to the auditors.
Script to determine permissions in SQL Server 2005
At times I run into application issues when I am unable to perform particular functions in the application that some of my peers are able to complete. I need to be able to quickly and easily understand my permissions in SQL Server. Do you know of an easy way to do so? Do you know how I can compare 2 different users to see what the difference could be at an instance, database or object level? Any and all suggestions would be appreciated.
Script to move all objects to a new schema for SQL Server
This tip includes a script that will identify and move all objects to a new SQL Server schema.
Secure and disable the SQL Server SA Account
Ideally your SQL instance would be configured to only allow for Windows Authentication. There may be times when mixed mode authentication is necessary at which point you will should configure a method to rotate the 'sa' password on a regular basis. You want the new password to be random and secure from others. Not only do you not want anyone else to know the password, you don’t even want to know it yourself.
Securing and protecting SQL Server data, log and backup files with TDE
I need to keep some old SQL data and log files in a backup folder for DR purposes. What is the best way to protect these files? In this tip we look at using TDE to safeguard your files and the steps necessary to move a TDE database to another SQL Server instance.
Security issues when using aliased users in SQL Server
SQL Server has a lot of little features that are nice to use, but sometimes these things come back to get you. One such issue is the use of aliased users. This tip shows you how to find security holes when aliased users are setup in your databases and also that this feature will be deprecated in SQL Server 2008.
Security Issues with the SQL Server BUILTIN Administrators Group
The principal of least privileges is a cornerstone to most security implementations. The premise behind the principal is to only grant users, developers, DBAs, network administrators, etc. the needed rights and nothing more. With SQL Server 2000 and 2005 one area that does not seem to follow this principal is related to the default rights for the BUILTIN\Administrators group.
Server level permissions for SQL Server 2005 and SQL Server 2008
I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the server level. What are they and what do they give rights to perform?
SQL Server 2005 Installation Log File Security Vulnerabilities
Where can I find the SQL Server 2005 installation log files to ensure no sensitive data, specifically clear text passwords, are stored in these files? I can remember from earlier versions of SQL Server that sensitive data, specifically clear text passwords, were stored in the installation and service pack log files. Is this the case with the SQL Server 2005 installation?
SQL Server 7.0 to 2005 Security Vulnerabilities Could Allow Elevation of Login Privileges
On a recent trip out west we were informed via radio that there were a series of earthquakes occurring off the coast of Vancouver Island. We happened to be sailing directly over the location of these earthquakes and never noticed anything, although the scientists tasked with monitoring such events were fully aware of the situation. You may wonder what this has to do with SQL Server. Well, you may not have been aware of the latest security release for SQL Server 2005 unless you were monitoring releases of this nature. At any rate, KB948109 was released in July and provides protection against vulnerabilities that would allow elevation of privileges by an attacker.
SQL Server Database Guest User Account
Who invited all of these guests to my database? Did you know that the guest user account even exists in your SQL Server databases? Do you know that SQL Server needs the guest user account for particular functionality that differs in SQL Server 2000 vs. 2005? Do you know if your SQL Server 2000 and 2005 databases have the guest login in their respective databases? Do you consider this a problem or the reality for your SQL Server environment?
SQL Server Login Failure Error 18456, Severity 14, State 10
Recently I experienced a situation where a SQL Server login failed with this error message:
Error: 18456, Severity: 14, State: 10.
Login failed for user 'SQL_Login'. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX].
Can you help me decipher this SQL Server issue and correct it? Check out this tip to learn more.
SQL Server Login Issue With Default Database
Every once in a while you may run into an issue where a user cannot login to SQL Server, because the logins default database may not be available. This could be for several reason such as that database was dropped, the database may be corrupt, the database is offline, the user no longer has access to the database or even because the database was renamed. When this happens the user will see an error message like the following on SQL Server 2005.
SQL Server nested securable permissions
I know that in SQL Server 2000 and below, you could assign permissions against objects like tables, views, and stored procedures. I'm hearing in SQL Server 2005 and 2008 there's a new security model called securables which allow for nestable permissions. How does this work?
SQL Server Security Audit Report
If your company needs to go through a SOX (Sarbanes–Oxley) audit or any security audit, the DBA has to provide security information to them. If you have purchased third party tools to provide this information that is great. If you don't have third party tools and need to go through many servers to provide this information it can be a hassle and very time consuming. So I put together a script to generate a report that I could just review. The script generates a report of all elevated level accounts and any possible security holes.
SQL Server separation of duties
In many organizations there is a need to segment what DBAs can do in certain environments. By default most DBAs are made sysadmins, but this gives them full control over the SQL Server instance. To meet certain regulatory guidelines this is not allowed, because DBAs have access to sensitive data. By default there is not a way to segment out certain DBA tasks vs. viewing user data, so in this tip we look at a Separation of Duty Framework that was developed to allow you to achieve this.
SQL Server Service Account Privileges
SQL Server 2005 Books Online indicates that the SQL Server service account requires permission to start the following related services (among many other requirements): SQL Server Active Directory Helper and SQL Server VSS Writer services. How can I verify service-related permissions for the SQL Server service account?
SQL Server Transparent Data Encryption (TDE) Performance Comparison
Every new release of SQL Server gives us new features that will hopefully improve our database system in some way. Data security has been a hot topic over the last few years and one of the new features introduced in SQL Server 2008, Transparent Data Encryption (TDE), addresses this topic. This feature gives us a way to secure our data on the backend without any front end code changes. The only concern I had with this new feature was exactly how much overhead would it produce on my server. This tip will take a look at the performance impact of implementing this feature using a few basic database operations.
Steps to Drop an Orphan SQL Server User when it owns a Schema or Role
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages: Msg 15421, Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropped. Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.
Suspect SQL Server 2000 Database (Part 1 of 2)
In your recent tip (Suspect SQL Server 2000 Database) you outlined a suspect database scenario. I took a look at the script and tried to run it in my environment. Unfortunately, this did not resolve my issue. Are you aware of other causes of SQL Server 2000 suspect databases? We have been working through a SOX audit and have been changing permissions at the file system level for our SQL Server. Could this type of change cause a suspect database? The timing of the permission change and the suspect databases correlate, but I am not sure if this is the true cause and affect.
Tables and Views for Auditing SQL Server Logins
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. In this tip we cover the tables and/or views that should be used to extract the information.
Unable to see a SQL Server table
I'm a developer and in a given database I can't see a table I know is there. The reason I know it's there is I can execute a stored procedure which queries the table and I never get an error executing it. What's going on?
Understanding Cross Database Ownership Chaining in SQL Server
I have a grasp on ownership chaining, but I'm wondering what cross-database ownership chaining is and how it works? How is the owner determined across databases if ownership is based on database users?
Understanding How A User Gets Database Access in SQL Server
I have been tasked with auditing security on my SQL Server. I understand that logins allow you to connect to SQL Server, but I'm not quite understanding how to determine whether a login has access to a database or not. For instance, I know that all logins can access the master database, but when I look at a login in SQL Server Management Studio, I don't see a checkbox beside the master db for that login. In this tip I show you how to determine what databases a login has access to.
Understanding SQL Server fixed database roles
I know there are fixed database roles that come with SQL Server. How do I best use them within my installations? What should I watch out for? In this tip we will cover each of the database roles and recommendations on when to and when not to use them.
Understanding SQL Server fixed server roles
I know there are fixed server roles that come with SQL Server. How do I best use them within my installations? What should I watch out for? In this tip we cover each of the fixed server roles as well as give you pointers on what to use and when to use them.
Understanding When SQL Server Kerberos Delegation is Needed
Recently, we were trying to setup a web application which uses Windows authentication. We want to pass the user's credentials through to the SQL Server because the database security is dependent on who the user is. However, we weren't able to make the connection. In the SQL Server error log it said the connection was being made by NT AUTHORITY\ANONYMOUS LOGON. In this tip we cover why this happens and how this can be resolved.
When not to use the sa password in SQL Server applications
Since SQL Server uses the sa SQL Server standard login when mixed mode authentication is selected during the installation process. Since it is a known and common entity, this login is a natural target to try to penetrate SQL Server. With many of the SQL Server installations using mixed mode authentication for custom application, web based applications or third party application needs, managing the usage and passwords for the sa login is critical. It is not something to take lightly, because this known login is a natural target with the highest privileges in SQL Server. As such, there are times that the sa login just should not be used. In this tip we will outline some of those times as the first installment of the 'sa series' from MSSQ
When was the last time the SQL Server sa password changed?
With the rights that the sa login has by default in SQL Server, it is imperative to change this password on a regular basis whether it is monthly, quarterly or semi-annually. In addition, as DBAs move on to other opportunities, it is wise to change the sa password as well. Changing the sa password should be a relatively easy process requiring little to no impact on the organization. Unfortunately, changing the sa password on a regular basis is not a common practice at most organizations because the impacts of changing the password are unknown.
Who is logging in as the sa login in SQL Server?
Not knowing which applications or people on your team that are using the sa login should be a major cause for concern in security conscious organizations. If the password for this standard SQL Server login is known by more than 1 team member, tracking changes committed by the sa login can be challenging. Since, the sa login has rights to execute all commands (highest level of rights in SQL Server) knowing who and what applications that are the sa login should cause concern for organizations that are less security conscious. Regardless of my situation, how can I find out who is using the sa login?
Why System Account is a bad idea for SQL Server Service Account
I'm trying to set up SQL Server and I know that I'm not supposed to use an account that's an administrator on the system, as per best practices. I see that the System account is a possibility, but I've been advised not to use it. Why not? Check out this tip to learn more.
Windows cannot access the specified device, path or file error
Every time I try to execute an executable (*.exe) file that I copied from a Windows 2003 File Server to my SQL Server running Windows 2003, I am unable to access the file. It works without a problem on the original Windows 2003 File Server and I am able to validate that nothing has changed. I just keep getting this error message - Windows cannot access the specified device, path or file. How can I resolve this issue? What am I doing wrong? I am just copying a file to my SQL Server.
Windows Groups to Support SQL Server 2005 Application Security
Have you had an issue trying to find out how to assign rights for some of the new applications (SQL Server Integration Services, Full Text, OLAP, Express, Reporting Services, etc.) in SQL Server 2005? If so, you are not alone. With SQL Server 2005, some of the new security is assigned via Windows Groups that can be managed directly via Computer Manager. Unfortunately, these are no where to be found or configured in SQL Server 2005 Management Studio (SSMS) in the same light as database engine permissions.
