| Test From Yahoo By Sathish |
Friday 25 December 2009
Wednesday 9 December 2009
Monday 30 November 2009
Restore
| |
| Tuesday, December 01, 2009 |
| Method 1:- RESTORE DATABASE NewDatabaseName FROM DISK='D:\YourBackupfilename.bak' WITH MOVE'LOgicalName of the SOurceDatafile' TO 'D:\NewDatabase.mdf', MOVE 'Logicalname of sourcelogfile' TO'D:\NewDatabase_log.ldf' |
| Method 2:- RESTORE DATABASE ntest_new FROM DISK = 'C:\temp\test.bak' WITH MOVE 'test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ntest_new.mdf' , MOVE 'test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ntest_new_log.ldf' |
| Method 3:- Retrive the Logical file name of the database from backup. |
| Method 4:- RESTORE DATABASE YourDB |
| Method 5:- RESTORE DATABASE Employee_Data_TSQL |
| Method 6:- RESTORE DATABASE Employee_Data_TSQL |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
Wednesday 25 November 2009
Joins
| A l l A b o u t J o i n s Step 1: Create Employee Table USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee]( [EmployeeID] [int] NOT NULL, [EmployeeName] [nchar](50) COLLATE Latin1_General_CI_AS NOT NULL, [ContactID] [int] NULL, [ManagerID] [int] NULL, [MaritalStatus] [nchar](1) COLLATE Latin1_General_CI_AS NULL, [HireDate] [datetime] NULL, CONSTRAINT [PK_FK_Employee_EmployeeID] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for employee rows' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'EmployeeID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the employee in the Contact table. Foreign key to Contact.ContactID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'ContactID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Married S = Single' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'MaritalStatus' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date on which the employee was hired' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'COLUMN', @level2name=N'HireDate' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Manager to whom the employee is assigned. Foreign key to Employee.EmployeeID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Employee', @level2type=N'CONSTRAINT', @level2name=N'PK_FK_Employee_EmployeeID' GO USE [Test] GO ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Contact] FOREIGN KEY([ContactID]) REFERENCES [dbo].[Contact] ([ContactID]) GO ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Manager] FOREIGN KEY([ManagerID]) REFERENCES [dbo].[Manager] ([ManagerID]) Step 2: Create EmployeeAddress Table USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[EmployeeAddress]( [EmployeeID] [int] NOT NULL, [AddressID] [int] NOT NULL, [Address] [nchar](70) COLLATE Latin1_General_CI_AS NOT NULL, CONSTRAINT [PK_EmployeeAddress] PRIMARY KEY CLUSTERED ( [AddressID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee identification number. Foreign key to Employee.EmployeeID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EmployeeAddress', @level2type=N'COLUMN', @level2name=N'EmployeeID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee address identification number. Foreign key to Address.AddressID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'EmployeeAddress', @level2type=N'COLUMN', @level2name=N'AddressID' GO USE [Test] GO ALTER TABLE [dbo].[EmployeeAddress] WITH CHECK ADD CONSTRAINT [FK_EmployeeAddress_Employee] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) Step 3: Insert Some values for both Employee and Employeeaddress tables Step 4 Join query Workouts Inner Join Examples SELECT E.EmployeeID, E.EmployeeName,E.ContactID,A.AddressID, A.address FROM Employee E INNER JOIN Employeeaddress A ON A.EmployeeID = E.EmployeeID WHERE A.addressID > 10 -- or = 10 or <> ORDER BY E.Employeename, A.address Inner Natural Join Examples SELECT E.*, A.addressID, A.address FROM Employee E INNER JOIN Employeeaddress A ON A.EmployeeID = E.EmployeeID Inner Equi Join Example: SELECT * FROM Employee E INNER JOIN EmployeeAddress A ON A.EmployeeID = E.EmployeeID Who is belongs to which manager select e1.Managername 'Manager',e2.employeename 'Employee' from Manager e1 join employee e2 on e1.managerid=e2.ManagerID Left Outer Join Example SELECT E.Employeename, E.ManagerID, C.Contactname FROM employee E LEFT OUTER JOIN contact C ON E.contactID = C.contactid ORDER BY E.employeename Right Outer Join Example SELECT E.Employeename, E.ManagerID, C.Contactname FROM employee E Right OUTER JOIN contact C ON E.contactID = C.contactid ORDER BY E.employeename --ORDER BY E.employeename, A.AU_FNAME FULL Outer Join Example SELECT E.Employeename, E.ManagerID, C.Contactname FROM employee E FULL OUTER JOIN contact C ON E.contactID = C.contactid ORDER BY E.employeename --ORDER BY E.employeename, A.AU_FNAME CROSS Join Example SELECT Employeename, EmployeeID, ContactName FROM Employee CROSS JOIN Contact ORDER BY Employeename LEFT OUTER JOIN that even returns the employees without managers SELECT t1.EmployeeName [Employee], COALESCE(t2.EmployeeName, 'No manager') [Manager] FROM Employee t1 LEFT OUTER JOIN Employee t2 ON t1.ManagerID = t2.EmployeeID |
Monday 23 November 2009
SQL Server Security Audit
SQL Server Security Audit
To find out what login accounts are members of this role [Working]
SELECT c.name AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = 'R'
AND a.name ='sysadmin'
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id
SELECT a.name AS Login_Name,
a.type_desc AS LoginType,
b.permission_name AS Permission
FROM sys.server_principals a
INNER JOIN sys.server_permissions b
ON a.principal_id = b.grantee_principal_id
WHERE a.type <> 'R' -- Excludes Server Role
AND a.name NOT LIKE '##MS_%' -- Excludes system level certificate mapped logins
AND a.name <> 'sa' -- Excludes the obvious system administrator
AND b.state = 'G' -- Granted
ORDER BY a.name
Result
GO
SELECT name as LoginName, type_desc AS LoginType
FROM sys.server_principals
WHERE type <> 'R' -- Excludes Server Role
AND name NOT LIKE '##MS_%' -- Excludes system level certificate mapped logins
AND name <> 'sa' -- Excludes the obvious system administrator
AND default_database_name = 'master'
DBCC Commands
Important DBCC Command-- Shrink Database
USE [Employee]
GO
DBCC SHRINKDATABASE(N'Employee', 4 )
GO
-- Find out the constraints in the current database
DBCC CHECKCONSTRAINTS-- Find out the constraints in the particular table DBCC CHECKCONSTRAINTS ('table1')DBCC CHECKCONSTRAINTS ('Employee')
-- To Clean the table space after a column is dropped from the database DBCC CLEANTABLE ('MyDatabase', 'MyTable')DBCC CLEANTABLE ('Company', 'Employee')
-- To Find the LogSpace
DBCC SQLPERF(LOGSPACE)
Exec ('DBCC SqlPerf (logspace)')
DBCC SqlPerf (UmsStats)
Exec ('DBCC SqlPerf (UmsStats)')
-- To retrivew the servers resources wait types
DBCC SqlPerf (WaitStats)
Exec ('DBCC SqlPerf (WaitStats)')
-- Retrieves information about your server’s outstanding
DBCC SqlPerf (IoStats)
Exec ('DBCC SqlPerf (IoStats)')
-- Retrieves information about your servers read-ahead statistics
DBCC SqlPerf (RaStats)
Exec ('DBCC SqlPerf (RaStats)')
-- Retrieves information for the server’s currently running threads
DBCC SqlPerf (Threads)
Exec ('DBCC SqlPerf (Threads)')
Sunday 22 November 2009
SQL Server Security
==========================================================
SQL Server Security
OBJECTIVE
SQL Server includes a variety of precise, configurable security features. These features empower administrators to implement defense-in-depth that is optimized for the specific security risks of their environment. This topic provides links to help you locate the security information that you need in the SQL Server Database Engine.
Securing SQL Server
Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. The following topics will guide you through creating and implementing an effective security plan.
Principals
Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).
Windows-level principals
• Windows Domain Login
• Windows Local Login
SQL Server-level principal
• SQL Server Login
Database-level principals
• Database User
• Database Role
• Role
The SQL Server SA Login
The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.
Public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.
Client and Database Server
By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.
Database Users
A database user is a principal at the database level. Every database user is a member of the public role.
Guest user
The guest user cannot be dropped, but it can be disabled by revoking its CONNECT permission. The CONNECT permission can be revoked by executing REVOKE CONNECT FROM GUEST within any database other than master or tempdb.
Application Roles
An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.
In SQL Server, application roles cannot access server-level metadata because they are not associated with a server-level principal. To disable this restriction and thereby allow application roles to access server-level metadata, set the global flag 4616.
Connecting with an Application Role
The following steps make up the process by which an application role switches security contexts:
- A user executes a client application.
- The client application connects to an instance of SQL Server as the user.
- The application then executes the sp_setapprole stored procedure with a password known only to the application.
- If the application role name and password are valid, the application role is enabled.
- At this point the connection loses the permissions of the user and assumes the permissions of the application role.
The permissions acquired through the application role remain in effect for the duration of the connection.
Syntax
CREATE APPLICATION ROLE application_role_name
WITH PASSWORD = 'password'
[ , DEFAULT_SCHEMA = schema_name ]
Real Time
CREATE APPLICATION ROLE Emp_salary
WITH PASSWORD = '987G^bv876sPY)Y5m23',
DEFAULT_SCHEMA = Employee; GO
Example:
USE [Employee] GO
CREATE APPLICATION ROLE [EmployeeSelect]
WITH DEFAULT_SCHEMA = [guest],
PASSWORD = N'employee@123' GO
USE [Employee] GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner]
TO [EmployeeSelect] GO
USE [Employee] GO
ALTER AUTHORIZATION ON SCHEMA::[Employee]
TO [EmployeeSelect] GO
Arguments
application_role_name
Specifies the name of the application role. This name must not already be used to refer to any principal in the database.
PASSWORD = 'password'
Specifies the password that database users will use to activate the application role. You should always use strong passwords. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.
DEFAULT_SCHEMA = schema_name
Specifies the first schema that will be searched by the server when it resolves the names of objects for this role. If DEFAULT_SCHEMA is left undefined, the application role will use DBO as its default schema. schema_name can be a schema that does not exist in the database.
User-Schema Separation
The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
New Behavior
The separation of ownership from schemas has important implications:
- Ownership of schemas and schema-scoped securable is transferable.
- Objects can be moved between schema's.
- A single schema can contain objects owned by multiple database users.
- Multiple database users can share a single default schema.
- Permissions on schemas and schema-contained securable can be managed with greater precision than in earlier releases.
- A schema can be owned by any database principal. This includes roles and application roles.
- A database user can be dropped without dropping objects in a corresponding schema.
- Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
- Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.
New Catalog Views
Schemas are explicit entities reflected in metadata; and as a result, schemas can only have one owner but a single user can own many schemas. This complex relationship is not reflected in the SQL Server 2000 system tables, so SQL Server 2005 introduced new catalog views, which accurately reflect the new metadata.
SQL Server 2005 and later catalog view
• sys.database_principals• sys.schemas
• sys.server_principals
Default Schema's
In order to resolve the names of securables that are not fully qualified names, SQL Server 2000 uses name resolution to check the schema owned by the calling database user and the schema owned by dbo. Beginning with SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.
===================================================================
Managing Logins, Users, and Schemas
The following topics that show you how to manage SQL Server logins, database users, and database schemas.
• Create a SQL Server Login
• Create a Database User
• Create a Database Schema
Create a SQL Server Login
To create a SQL Server login that uses Windows Authentication (SQL Server Management Studio)
- In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
- Right-click the Security folder, point to New, and then click Login.
- On the General page, enter the name of a Windows user in the Login name box.
- Select Windows Authentication.
- Click OK.
Using Script
Syntax
CREATE LOGIN
Real Time
USE [master] GO
CREATE LOGIN [RESEARCH\Guest]
FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO
To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio)
1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter a name for the new login in the Login name box.
4. Select SQL Server Authentication. Windows Authentication is the more secure option.
5. Enter a password for the login.
6. Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.
7. Click OK.
Examples:
Using Script
Syntax
CREATE LOGIN [LOGIN NAME] WITH PASSWORD = ''
Real Time
USE [master] GO CREATE LOGIN [RESEARCH\Guest]
WITH PASSWORD=N'winter@77' MUST_CHANGE,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO
===========================================================
Create a Database UserThis session shows how to create a database user mapped to a SQL Server login. The topic assumes that a corresponding SQL Server login already exists.
To create a database user using SQL Server Management Studio
1. In SQL Server Management Studio, open Object Explorer and expand the Databases folder.
2. Expand the database in which to create the new database user.
3. Right-click the Security folder, point to New, and then click User.
4. On the General page, enter a name for the new user in the User name box.
5. In the Login name box, enter the name of a SQL Server login to map to the database user.
6. Click OK.
Using Script
Syntax
USE [database name] GO
CREATE USER [new user name] FOR LOGIN [login name]; GO
Real Time
USE [Employee]
GO
CREATE USER [Abcdef] FOR LOGIN [winter\victor]
GO
================================================================Create a Database Schema
To create a schema by using SQL Server Management Studio
1.In SQL Server Management Studio, open Object Explorer and expand the Databases folder. 2.Expand the database in which to create the new database schema. 3.Right-click the Security folder, point to New, and then click Schema. 4.On the General page, enter a name for the new schema in the Schema name box. 5.In the Schema owner box, enter the name of a database user or role to own the schema. 6.Click OK.
Using Script
Syntax
USE [database name] GO
CREATE SCHEMA [new schema name]
AUTHORIZATION [new schema owner]; GO
Real Time
USE [Employee] GO
CREATE SCHEMA [Schemavictor]
AUTHORIZATION [ownvictor] GO
===================================================================SERVER-LEVEL ROLES
To easily manage the permissions on SQL server, SQL Server provides several roles, which are security principals that group other principals. Roles are like groups in the Microsoft Windows operating system.
Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope. You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role. The following table shows the server-level roles and their capabilities.
Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope.
You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role.
The following table shows the server-level roles and their capabilities.
sysadmin
Description: Members of the sysadmin fixed server role can perform any activity in the server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor, @rolename = N'sysadmin'
GO
Serveradmin
Description: Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'serveradmin'
GO
securityadmin
Description: Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'securityadmin'
GO
processadmin
Description: Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'processadmin'
GO
setupadmin
Description: Members of the setupadmin fixed server role can add and remove linked servers.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'setupadmin' GO
bulkadmin
Description: Members of the bulkadmin fixed server role can run the BULK INSERT statement.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'bulkadmin'
GO
diskadmin
Description: The diskadmin fixed server role is used for managing disk files.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'diskadmin'
GO
dbcreator
Description: Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'dbcreator'
GO
Public
Description: Every SQL Server login belongs to the public server role. When a server
principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the objectto be available to all users.===================================================================To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.
There are two types of database-level roles in SQL Server: fixed database roles that are predefined in the database and flexible database roles that you can create.
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_security admin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also some special-purpose fixed database roles in themsdb database.
You can add any database account and other SQL Server roles into database-level roles. Each member of a fixed database role can add other logins to that same role.
db_owner
Description: Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database
db_securityadmin
Description: Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin
Description: Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator
Description: Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin
Description: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter
Description: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader
Description: Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter
Description: Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader
Description: Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.
==================================================Password Policy
SQL Server provides several tools for configuring SQL Server features, connections, and services. This topic explains the tools that you can use to enable and disable features, such as stored procedures, Windows services, Web services, and remote client connectivity.
When it is running on Windows Server 2003 or later, SQL Server can use Windows password policy mechanisms.
SQL Server can apply the same complexity and expiration policies used in Windows Server 2003 to passwords used inside SQL Server. This functionality depends on theNetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later versions.
Password Complexity
Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:
• The password does not contain all or part of the account name of the user. Part of an account name is defined as three or more consecutive alphanumeric characters delimited on both ends by white space such as space, tab, and return, or any of the following characters: comma (,), period (.), hyphen (-), underscore (_), or number sign (#).
• The password is at least eight characters long.
• The password contains characters from three of the following four categories:
• Latin uppercase letters (A through Z)
• Latin lowercase letters (a through z)
• Base 10 digits (0 through 9)
• Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%)
Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.
Password Expiration
Password expiration policies are used to manage the lifespan of a password.
When SQL Server enforces password expiration policy, users are reminded to change old passwords, and accounts that have expired passwords are disabled.
Policy Enforcement
The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:
• When CHECK_POLICY is changed to ON, the following behaviors occur:
• CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
• The password history is initialized with the value of the current password hash.
• When CHECK_POLICY is changed to OFF, the following behaviors occur:
• CHECK_EXPIRATION is also set to OFF.
• The password history is cleared.
• The value of lockout_time is reset.
Some combinations of policy options are not supported.
• If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
• If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.
ImportantCHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later versions.
A known issue in Windows Server 2003 might prevent the bad password count from being reset after Lockout Threshold has been reached. This might cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON.
When SQL Server is running on Windows 2000, setting CHECK_POLICY = ON will prevent the creation of passwords that are:
• Null or empty
• Same as name of computer or login
• Any of the following: "password", "admin", "administrator", "sa", "sysadmin"
The security policy might be set in Windows, or might be received from the domain. To view the password policy on the computer, use the Local Security Policy MMC snap-in (secpol.msc).
Script:
USE [master]
GO
CREATE LOGIN [victor] WITH PASSWORD=N'winter123' MUST_CHANGE,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO
Strong Password
Passwords can be the weakest link in a server security deployment. You should always take great care when you select a password. A strong password has the following characteristics:
• Is at least 8 characters long.
• Combines letters, numbers, and symbol characters within the password.
• Is not found in a dictionary.
• Is not the name of a command.
• Is not the name of a person.
• Is not the name of a user.
• Is not the name of a computer.
• Is changed regularly.
• Is significantly different from previous passwords.
Microsoft SQL Server passwords can contain up to 128 characters, including letters, symbols, and digits. Because logins, user names, roles, and passwords are frequently used in Transact-SQL statements, certain symbols must be enclosed by double quotation marks (") or square brackets ([ ]). Use these delimiters in Transact-SQL statements when the SQL Server login, user, role, or password has the following characteristics:
• Contains or starts with a space character.
• Starts with the $ or @ character.
If used in an OLE DB or ODBC connection string, a login or password must not contain the following characters: [] {}(),;? * ! @. These characters are used to either initialize a connection or separate connection values.
Conclusion
The SQL Server Security offers many core tasks and options for Server, database, table...etc. It’s very quick and intuitive to use.===================================================================
Subscribe to:
Posts (Atom)