:::: MENU ::::

Posts Categorized / Logins and Users

  • Nov 05 / 2008
  • 0
dbDigger, Logins and Users, Security and Permissions

Configure Database State and restrict access in SQL Server

Database Read-Only (READ_ONLY or READ_WRITE)
You can use Database Read-Only option to control whether updates are allowed on the database. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, users can only read from the database and are not permitted to modify data. When set to FALSE, the read and write operations are permitted on the database. To change the state of this option, you must have exclusive access to the database.

Database State (DB_STATE_OPTION)
You can use Database State option to control the state of the database. When set to NORMAL, the database is fully operational and available for use. When the database is set to CLOSED, the database is shut down cleanly and marked offline. The database cannot be accessed or modified while in this state. When set to EMERGENCY, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. The EMERGENCY database state is used primarily for troubleshooting purposes.

Restrict Access can be used this option to control access to the database. In MULTIPLE mode all users are allowed to connect to the database as long as they have the appropriate permissions. Conversely, in the SINGLE mode only one user is permitted to access the database at a time. In the RESTRICTED mode only members of the db_owner, dbcreator, and sysadmin roles can connect to the database.

  • Oct 30 / 2008
  • 0
dbDigger, Logins and Users, Security and Permissions

Securables in SQL Server 2005

Database objects aren’t the only things that you can secure. You can create rights and privileges for everything from the server itself to each database and each object that they contain. Securables in SQL Server 2005 are the resources to which the SQL Server Database Engine authorization system regulates access. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured. You can secure at server level, database level and schema level. The securable scopes are

  • server
  • database
  • schema

Securable scope Server contains following securables on SQL Server 2005

  • Endpoint
  • Login
  • Database

Securable scope database contains the following securables on SQL Server 2005

  • User
  • Role
  • Application role
  • Assembly
  • Message Type
  • Route
  • Service
  • Remote Service Binding
  • Fulltext Catalog
  • Certificate
  • Asymmetric Key
  • Symmetric Key
  • Contract
  • Schema

Securable scope schema contains the following securables on SQL Server 2005

  • Type
  • XML Schema Collection
  • Object

SQL Server Objects
The following securables are members of the object class for SQL Server 2005

  • Aggregate
  • Constraint
  • Function
  • Procedure
  • Queue
  • Statistic
  • Synonym
  • Table
  • View
  • Aug 27 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

When you last changed password of sa

Changing the passwords regularly is an important requirement of robust security implementation. And it becomes more important when login is sa. To analyze that when last time you implemented this good practice, use following script.

USE Master  
 SELECT sid, [name], createdate, updatedate  
 FROM master.dbo.syslogins  
 WHERE [name] = 'sa'  

And if specific to SQL Server 2005 then

USE Master  
 SELECT [name], sid, create_date, modify_date  
 FROM sys.sql_logins  
 WHERE [name] = 'sa'  

The script will return name, sid, creation date and update/modification date of all of your logins. As sa account can not be updated/modified except that of password, so update date here will be the date when password of sa was changed. And if no name condition is provided then update date tells you any last any update for other logins.

USE Master  
 SELECT [name], sid, create_date, modify_date  
 FROM sys.sql_logins  
  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, Logins and Users, Security and Permissions

DBA Best Practices for SQL Server Security

Following best practices may be implemented as base line for standard security of SQL Server

  1. Ensure the physical security of each SQL Server, preventing any unauthorized users to physically accessing your servers.
  2. Only install required network libraries and network protocols on your SQL Server instances.
  3. Minimize the number of sysadmins allowed to access SQL Server.
  4. As a DBA, log on with sysadmin privileges only when needed. Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.
  5. Assign the SA account a very obscure password, and never use it to log onto SQL Server. Use a Windows Authentication account to access SQL Server as a sysadmin instead.
  6. Give users the least amount of permissions they need to perform their job.
  7. Use stored procedures or views to allow users to access data instead of letting them directly access tables.
  8. When possible, use Windows Authentication logins instead of SQL Server logins.
  9. Use strong passwords for all SQL Server login accounts.
  10. Don’t grant permissions to the public database role.
  11. Remove user login IDs who no longer need access to SQL Server.
  12. Remove the guest user account from each user database.
  13. Disable cross database ownership chaining if not required.
  14. Never grant permission to the xp_cmdshell to non-sysadmins.
  15. Remove sample databases from all production SQL Server instances.
  16. Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
  17. Avoid creating network shares on any SQL Server.
  18. Turn on login auditing so you can see who has succeeded, and failed, to login.
  19. Don’t use the SA account, or login IDs who are members of the Sysadmin group, as accounts used to access SQL Server from applications.
  20. Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
  21. Remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. Before you do this on a clustered SQL Server, check Books Online for more information.
  22. Run each separate SQL Server service under a different Windows domain account
  23. Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed. SQL Server setup will automatically configure service accounts with the necessary permissions for them to run correctly, you don’t have to do anything.
  24. When using distributed queries, use linked servers instead of remote servers.
  25. Do not browse the web from a SQL Server.
  26. Instead of installing virus protection on a SQL Server, perform virus scans from a remote server during a part of the day when user activity is less.
  27. Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
  28. Encrypt all SQL Server backups with a third-party backup tool, such as SQL Backup Pro.
  29. Only enable C2 auditing or Common Criteria compliance if required.
  30. Consider running a SQL Server security scanner against your SQL servers to identify security holes.
  31. Consider adding a certificate to your SQL Server instances and enable SSL or IPSEC for connections to clients.
  32. If using SQL Server 2005, enable password policy checking.
  33. If using SQL Server 2005, implement database encryption to protect confidential data.
  34. If using SQL Server 2005, don’t use the SQL Server Surface Area Configuration tool to unlock features you don’t absolutely need.
  35. If using SQL Server 2005 and you create endpoints, only grant CONNECT permissions to the logins that need access to them. Explicitly deny CONNECT permissions to endpoints that are not needed by users.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

Consult us to explore the Databases. Contact us