Recent Articles

Auto Generate a security report on SQL Server instance – Security script

| April 29, 2017 | 0 Comments

— Srored Procedure to generate Security Audit report in HTML format:

CREATE PROC spAuditUsersPermissions

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

DECLARE @strHTML VARCHAR(MAX)

DECLARE @i INT

DECLARE @rc INT

DECLARE @dbname VARCHAR(400)

—————–Print header of the report——————–

SELECT @strHTML = ‘<HTML><HEAD><TITLE> Security list for the auditor </TITLE><STYLE>TD.Sub{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} BODY{FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TABLE{BORDER-BOTTOM: 1pt solid #000000;BORDER-LEFT: 0pt solid #000000;BORDER-RIGHT: 1pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD{BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD.Title{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 12pt} A.Index{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#000099;FONT-FAMILY:Tahoma;TEXT-DECORATION:none} A.Index:HOVER{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#990000;FONT-FAMILY:Tahoma;TEXT-DECORATION:none}</STYLE></HEAD><BODY><A NAME=”_top”></A><BR>’

PRINT @strHTML

—————–Login information————————————————————-

SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber,

name, dbname,language,

CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN ‘X’ ELSE ‘–‘ END) AS IsDenied,

CONVERT(CHAR(10),CASE isntname WHEN 1 THEN ‘X’ ELSE ‘–‘ END) AS IsWinAuTHENtication,

CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN ‘X’ ELSE ‘–‘ END) AS IsWinGroup,

createdate,UPDATEdate,

CONVERT(VARCHAR(2000),

CASE sysadmin WHEN 1 THEN ‘sysadmin,’ ELSE ” END +

CASE securityadmin WHEN 1 THEN ‘securityadmin,’ ELSE ” END +

CASE serveradmin WHEN 1 THEN ‘serveradmin,’ ELSE ” END +

CASE setupadmin WHEN 1 THEN ‘setupadmin,’ ELSE ” END +

CASE processadmin WHEN 1 THEN ‘processadmin,’ ELSE ” END +

CASE diskadmin WHEN 1 THEN ‘diskadmin,’ ELSE ” END +

CASE dbcreator WHEN 1 THEN ‘dbcreator,’ ELSE ” END +

CASE bulkadmin WHEN 1 THEN ‘bulkadmin’ ELSE ” END ) AS ServerRoles

INTO #syslogins

FROM master..syslogins WITH (nolock)

ORDER BY name

SET @rc = @@rowcount

SELECT @strHTML = ‘<BR><CENTER><FONT SIZE=”5″><B> Server ‘ + @@servername + ‘</B></FONT></CENTER><BR>’

PRINT @strHTML

PRINT ‘<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”100%”>’

— Query the data only if there are rows:

IF @rc = 0

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”1″ ALIGN=”center”><B><A NAME=”_LoginInfomration”>Logins information</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>There are no logins on this server</B> </TD></TR>’

END

ELSE

BEGIN

UPDATE #syslogins

SET ServerRoles = SUBSTRING(ServerRoles,1,LEN(ServerRoles)-1)

WHERE SUBSTRING(ServerRoles,LEN(ServerRoles),1) = ‘,’

UPDATE #syslogins SET ServerRoles = ‘–‘

WHERE LTRIM(RTRIM(ServerRoles)) = ”

PRINT ‘<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”100%”>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”9″ ALIGN=”center”><B><A NAME=”_LoginInfomration”>Logins information</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”50%”><B>Login Name</B> </TD><TD ALIGN=”left” WIDTH=”50%”><B>Default DB</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Language</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Denied acess?</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Windows Auth?</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Window group?</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Date created</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Date UPDATEd</B> </TD><TD AALIGN=”left” WIDTH=”770%”><B>Server roles</B> </TD></TR>’

SET @i = 1

WHILE @i <= @rc

BEGIN

SELECT @strHTML =

‘<TR><TD><B>’ + CONVERT(VARCHAR(50),name) + ‘</B> </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),CASE ISNULL(dbname,’–‘) WHEN ” THEN ‘–‘ ELSEISNULL(dbname,’–‘) END) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(language,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(10),ISNULL(IsDenied,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(30),ISNULL(createdate,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(100),ISNULL(ServerRoles,’–‘)) + ‘ </TD>’ +

‘</TR>’

FROM #syslogins

WHERE RowNumber = @i

PRINT @strHTML

SET @i = @i + 1

END

PRINT ‘</TABLE></DIV><BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’

PRINT'<BR><CENTER></CENTER><BR>’

END

DROP TABLE #syslogins

—————Fetch data per database————————————————-

CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL)

CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL)

CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerTypeVARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50),

ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10))

CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermTypeVARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5))

DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name

OPEN dbs

FETCH NEXT FROM dbs INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

TRUNCATE TABLE #LoginMap

TRUNCATE TABLE #RoleUser

TRUNCATE TABLE #ObjectPerms

TRUNCATE TABLE #DatabasePerms

SELECT @strHTML = ‘<BR><CENTER><FONT SIZE=”5″><B> Database ‘ + @dbname + ‘</B></FONT></CENTER><BR>’

PRINT @strHTML

—————–Mapping of logins to users——————

EXEC(‘

INSERT INTO #LoginMap

SELECT login.loginname,users.name

FROM [‘+ @dbname+’].dbo.sysusers users

INNER JOIN [master].[dbo].[syslogins] login

ON users.[sid] = login.[sid]

WHERE users.uid < 16382

and users.name not in (”public”,”dbo”,”guest”)

‘)

SET @strHTML = ”

PRINT ‘<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”60%”>’

–Query the data only if there are rows

IF NOT EXISTS (SELECT 1 FROM #LoginMap)

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”1″ ALIGN=”center”><B><A NAME=”_LoginMapping”>Mapping of logins to users</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>There are no mappings in this database</B> </TD></TR>’

END

ELSE

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”2″ ALIGN=”center”><B><A NAME=”_LoginMapping”>Mapping of logins to users</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>Login Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>User Name</B> </TD></TR>’

SELECT @strHTML = @strHTML +

‘<TR><TD><B>’ + CONVERT(VARCHAR(50),LoginName) + ‘</B> </TD><TD>’ +CONVERT(VARCHAR(50),ISNULL(UserName,”)) + ‘ </TD></TR>’ + CHAR(10)

FROM #LoginMap

ORDER BY LoginName

PRINT @strHTML

END

PRINT ‘</TABLE></DIV><BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’

—————-SQL roles per user——————

EXEC (‘INSERT INTO #RoleUser

SELECT b.name AS Role_name, a.name AS User_name ‘ +

‘FROM [‘+ @dbname+’]..sysusers a ‘ +

‘INNER JOIN [‘+ @dbname+ ‘]..sysmembers c on a.uid = c.memberuid ‘ +

‘INNER JOIN [‘+ @dbname+ ‘]..sysusers b ON c.groupuid = b.uid ‘ +

‘WHERE a.name <> ”dbo”’

)

SET @strHTML = ”

PRINT ‘<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”60%”>’

— Query the data only if there are rows:

IF NOT EXISTS(SELECT 1 FROM #RoleUser)

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”1″ ALIGN=”center”><B><A NAME=”_DBRoleMapping”>Roles per user</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>There are no users mapped to roles in this database</B> </TD></TR>’

END

ELSE

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”2″ ALIGN=”center”><B><A NAME=”_DBRoleMapping”>Roles per user</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>Role Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>User Name</B> </TD></TR>’

SELECT @strHTML = @strHTML +

‘<TR><TD><B>’ + CONVERT(VARCHAR(50),RoLEName) + ‘</B> </TD><TD>’ +CONVERT(VARCHAR(50),ISNULL(UserName,”)) + ‘ </TD></TR>’ + CHAR(10)

FROM #RoleUser

ORDER BY RoLEName

PRINT @strHTML

END

 

PRINT ‘</TABLE></DIV><BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’

—————-Database level Permissions————————-

EXEC (‘INSERT INTO #DatabasePerms

(UserName,PermType,PermName,IsGrantOption)

SELECT usr.name,

CASE WHEN perm.state <> ”W” THEN perm.state_desc ELSE ”GRANT” END,

perm.permission_name,

CASE WHEN perm.state != ”W” THEN ”–” ELSE ”X” END AS IsGrantOption

FROM [‘+@dbname+’].sys.database_permissions AS perm

INNER JOIN

[‘+@dbname+’].sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

WHERE perm.major_id = 0

ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC’

)

SET @rc = @@rowcount

PRINT ‘<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”60%”>’

— Query the data only if there are rows:

 

IF NOT EXISTS(SELECT 1 FROM #DatabasePerms)

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”1″ ALIGN=”center”><B><A NAME=”_DBLvlPerms”>Database level permissions</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>There are no specific permissions on the database level</B> </TD></TR>’

END

ELSE

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=” 4″ ALIGN=”center”><B><A NAME=”_DBPObjPerms”>Database level permissions</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>User Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Permission type</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Permission Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Grant option?</B> </TD></TR>’

 

SET @i = 1

WHILE @i <= @rc

BEGIN

SELECT @strHTML =

‘<TR><TD><B>’ + CONVERT(VARCHAR(50),UserName) + ‘</B> </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(PermType,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(PermName,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,’–‘)) + ‘ </TD>’+

‘</TR>’

FROM #DatabasePerms

WHERE Rownumber = @i

PRINT @strHTML

 

SET @i = @i + 1

END

END

PRINT ‘</TABLE></DIV><BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’

—————-Database object Permissions————————-

EXEC (‘INSERT INTO #ObjectPerms

(UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption)

SELECT usr.name AS UserName,

CASE WHEN perm.state <> ”W” THEN perm.state_desc ELSE ”GRANT” END AS PerType,

perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName,

CASE obj.Type

WHEN ”U” THEN ”Table”

WHEN ”V” THEN ”View”

WHEN ”P” THEN ”Stored Proc”

WHEN ”FN” THEN ”Function”

ELSE obj.Type END AS ObjectType,

CASE WHEN cl.column_id IS NULL THEN ”–” ELSE cl.name END AS ColName,

CASE WHEN perm.state = ”W” THEN ”X” ELSE ”–” END AS IsGrantOption

FROM [‘+@dbname+’].sys.database_permissions AS perm

INNER JOIN

[‘+@dbname+’].sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

[‘+@dbname+’].sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

[‘+@dbname+’].sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE obj.Type <> ”S”

ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC’

)

SET @rc = @@rowcount

PRINT ‘<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”60%”>’

 

— Query the data only if there are rows:

IF NOT EXISTS(SELECT 1 FROM #ObjectPerms)

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”1″ ALIGN=”center”><B><A NAME=”_DBPObjPerms”>Object permissions</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>There are no specific permissions to objects in this database</B> </TD></TR>’

END

ELSE

BEGIN

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”8″ ALIGN=”center”><B><A NAME=”_DBPObjPerms”>Object permissions</A></B> </TD></TR>’

PRINT ‘<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”><B>User Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Permission type</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Permission Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Schema Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Object Name</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Object type type</B> </TD><TD ALIGN=”left” WIDTH=”70%”><B>Column Name</B> </TD><TD ALIGN=” left” WIDTH=”70%”><B>Grant option?</B> </TD></TR>’

 

SET @i = 1

WHILE @i <= @rc

BEGIN

SELECT @strHTML =

‘<TR><TD><B>’ + CONVERT(VARCHAR(50),UserName) + ‘</B> </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(PerType,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(PermName,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(SchemaName,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(ObjectName,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(30),ISNULL(ObjectType,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(50),ISNULL(ColName,’–‘)) + ‘ </TD>’ +

‘<TD>’ + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,’–‘)) + ‘ </TD></TR>’

FROM #ObjectPerms

WHERE Rownumber = @i

PRINT @strHTML

SET @i = @i + 1

END

END

PRINT ‘</TABLE></DIV><BR><A CLASS=”Index” HREF=”#_top”>Back To Top ^</A><BR><BR>’

FETCH NEXT FROM dbs INTO @dbname

END

—————Close cursor and drop all temporary objects————-

CLOSE dbs

DEALLOCATE dbs

DROP TABLE #LoginMap

DROP TABLE #RoleUser

DROP TABLE #ObjectPerms

DROP TABLE #DatabasePerms

PRINT ‘</BODY></HTML>’

GO

—————For the result- Execute the stored procedure ————-

USE [Retail Assist 1]

GO

DECLARE     @return_value int

EXEC  @return_value = [dbo].[spAuditUsersPermissions]

SELECT      ‘Return Value’ = @return_value

GO

Disable SQL Server SA account

Disable SQL Server SA account

| April 29, 2017 | 0 Comments

EXEC sp_SetAutoSAPasswordAndDisable

 

GO
This system procedure disables sa account and changes password with a random one. Sometimes this could be necessary for security policies. It is to roll your own solution to rotate the password for the sa login.

 

Find out more on our ebook SQL Server Specialists Guide. Download it here.

SQL Server Database level permission

SQL Server Database level permission

| April 29, 2017 | 0 Comments

SELECT prin.[name] [User], sec.state_desc + ‘ ‘ + sec.permission_name [Permission]

FROM [sys].[database_permissions] sec

JOIN [sys].[database_principals] prin

ON sec.[grantee_principal_id] = prin.[principal_id]

WHERE sec.class = 0

ORDER BY [User], [Permission];

 

Find out more on our ebook SQL Server Specialists Guide. Download it here.

Permissions granted or denied in a SQL Server Database

Permissions granted or denied in a SQL Server Database

| April 29, 2017 | 0 Comments

SELECT

perms.state_desc AS State,

permission_name AS [Permission],

obj.name AS [on Object],

dPrinc.name AS [to User Name],

sPrinc.name AS [who Logged in name]

FROM sys.database_permissions AS perms

JOIN sys.database_principals AS dPrinc

ON perms.grantee_principal_id = dPrinc.principal_id

JOIN sys.objects AS obj

ON perms.major_id = obj.object_id

LEFT OUTER JOIN sys.server_principals AS sPrinc

 

ON dPrinc.sid = sPrinc.sid

To return the explicit permissions granted or denied in a database, execute this statement in the database.

 

Find out more on our ebook SQL Server Specialists Guide. Download it here.

SQL Server Script to find out currently running SQL

SQL Server Script to find out currently running SQL

| April 29, 2017 | 0 Comments

SELECT sqltext.TEXT,

req.session_id,

req.status,

req.command,

req.cpu_time,

req.total_elapsed_time

FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;

Result :

TEXT session_id status command cpu_time total_elapsed_time

Find out more on our ebook SQL Server Specialists Guide. Download it here.

SQL CHECK Constraint

SQL CHECK Constraint

| April 29, 2017 | 0 Comments

Find out more on our ebook SQL Server Specialists Guide. Download it here.

 

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.

CREATE TABLE [dbo].[audit_action_Tab](

[table_name] [varchar](30) NULL,

[action_id] [varchar](30) NULL,

[proc_name] [varchar](40) NULL,

[pkg_name] [varchar](40) NULL,

[dml_type] [varchar](1) NULL,

[desc] [varchar](200) NULL

) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’DML type:

S -SELECT,

I -INSERT,

U-UPDATE,

D- DELETE’ , @level0type=N’SCHEMA’,@level0name=N’dbo’,@level1type=N’TABLE’,@level1name=N’audit_action_Tab’,@level2type=N’COLUMN’,@level2name=N’dml_type’

GO

ALTER TABLE [dbo].[audit_action_Tab]  WITH CHECK ADD  CONSTRAINT [dml_type_CK] CHECK (([dml_type]=’S’ OR [dml_type]=’I’ OR [dml_type]=’U’ OR [dml_type]=’D’))

GO

ALTER TABLE [dbo].[audit_action_Tab] CHECK CONSTRAINT [dml_type_CK]

GO

DROP a CHECK constraint

 

Find out more on our ebook SQL Server Specialists Guide. Download it here.

Create Index on Views in SQL Server

Create Index on Views in SQL Server

| April 29, 2017 | 0 Comments

USE yourdb

GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[SampleView]’))

DROP VIEW [dbo].[SampleView]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[SampleTable]’) AND TYPEIN (N’U’))

DROP TABLE [dbo].[SampleTable]

GO

— Create SampleTable

CREATE TABLE SampleTable1 (Col1 INT, Col2 INT, F_Name VARCHAR(100))

INSERT INTO SampleTable1 (Col1,Col2,F_Name)

SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY o1.name),

ROW_NUMBER() OVER (ORDER BY o2.name),

o2.name

FROM sys.all_objects o1

CROSS JOIN sys.all_objects o2

GO

— Create View

CREATE VIEW SampleView

WITH SCHEMABINDING

AS

SELECT Col1,Col2,Name

FROM dbo.SampleTable1

GO

— Create Index on View

CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]

(

Col2 ASC

)

GO

— Select from view

SELECT Col1,Col2,F_Name

FROM SampleView

ORDER BY Col2

GO

Here ORDER BY clause uses the Index created on the View.

Find out more on our ebook SQL Server Specialists Guide. Download it here.

Welcome to SQL Server Specialists

Welcome to SQL Server Specialists

| April 29, 2017 | 0 Comments

SQL is a standard language for storing, manipulating and retrieving data in databases. Our SQL ebook will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems. Our downloadable guide is written by our SQL Server Specialists and the tutorial explains  basic and advanced concepts of SQL Server such as how to create and restore data, create login and backup, assign permissions, etc. Each topic is explained using examples for easy understanding. If you are looking for an affordable guide, you’ll learn easily and fast SQL Server language, coding and functions. Please CLICK HERE to download SQL Server Specialists Ebook.