SQL CHECK Constraint in SQL Server

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

---

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.
MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

---

SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

---

To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

Duplicate removal in Table

T-SQL:

with a
as
(
select * from ( select row_number() over(partition by emp_id order by emp_id)[dup],* from Employee_Test)[samp]
)
delete from a where [dup]>1-------->to remove
--select * from a where [dup]>1----> to see 

Use database inside stored procedure

 Use database inside stored procedure

I am thinking about an important funcitonality or property in sql server.
Is it possible to give the database name dynamically in sql server?
OOps, i think we can achieve this using dynamic sql.
I have made a try on this. Lets see,
use master
GO
declare @sql nvarchar(max)
select @sql = 'use'
select @sql = @sql + ' ' + 'Northwind'
select * from dbo.Employees -- Northwind table
exec sp_executesql @sql
--------------------------------------------------------
Its not working. I have tried to analyse this problem, its because once the query gets executed its coming out of that particular scope and enter into the public scope.

The work around for this problem is, we need to

use tempdb
go
declare @sql nvarchar(1000)
declare @dbname varchar(40)
select @dbname = 'Northwind'
select db_name() --tempdb
select @sql = 'use'
select @sql = @sql + ' '+@dbname +' select * from dbo.Employees
select db_name()'
exec sp_executesql @sql --northwind
select db_name() --tempdb

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.



The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.


(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Solution:

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

1. The issue may be due to firewall.
2. SQL browser service is not running
3. SQL Server is not configured to allow remote connection
4. Check whether tcp/ip protocol is enabled for communication in sql server configuration manager -> Client and server protocols
5. Check tcp/ip protocols are enabled and active.
6. check whether you sql server logins are properly created and provided necessary level of authentication.

Create windows authentication user in SQL Server

Creating/Configuring Windows authetication in SQL Server:

I got a peculiar query in one of the SQL forum and I wish to write a detailed article on this. In SQL Server, there are 3 types of authentication to enter into your database.

1. Windows authentication. (Will take the in built windows account as an authentication)
2. SQL Server authenticaiton (We need to specify the username and password to enter into the server)
3. Mixed mode authentication (Combination of windows and SQL authentication)

Goto Server Explorer -> Security -> Login ->New Login

Click on the Search button and a window will popup to search for the available users in the machine. In the below screenshot, my machine name is venkat and santhi is an administrator in my machine. I am trying to create windows authentication for this user.

Select the "Server Roles", am trying to make this use as sysadmin.

Select the database to which the user needs proper access.

On clicking "OK" the user is created.

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information Resolved issues

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information

In one of our recent migrations, we got the following error when the client tried to fire xp_cmdshell system stored procedure through some client code.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

When xp_cmdshell is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

So this is what we did:

1	EXEC sp_xp_cmdshell_proxy_account 'HOCBASE\admin', 'account_password';
2	GO

The effect:

And things worked fine.

 A few more things:

How do you drop the proxy credential?

1	EXEC sp_xp_cmdshell_proxy_account NULL;
2	GO

How can you find all the users (except sysadmins) who can execute or have access to xp_cmdshell?

1	Use master
2	EXEC sp_helprotect 'xp_cmdshell'

And who are all the sysadmins on the box?

1	Use master
2	EXEC sp_helpsrvrolemember 'sysadmin'

To see list of Login used objects or login Dependent objects like SPs,views,tables,udf in SQL

To see Login used objects like SPs,views,tables,udf in SQL:

Select [UserName],[UserType],[PermissionType],[ObjectType],[ObjectName] from 
(
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    --roleprinc.[type] = 'R' AND
    --Only public role
    --roleprinc.[name] = 'username' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0

)as a
where 1=1 
and [ObjectName] in ('spname')
and a.[UserName] = 'username'