The SQL Server and .NET Hub

Search our hub for SQL Server and .NET knowledge!

Monday, January 16, 2017

Getting Started with SQL Server In-Memory OLTP – Part 1

In-Memory OLTP in SQL Server 2014 or later is a powerful engine integrated into the SQL Server Engine, optimized for Online Transaction Processing (OLTP).


In-Memory OLTP (codenamed “Hekaton”) is a powerful engine which is fully integrated into SQL Server’s Database Engine and introduces new data structures for optimizing the performance of OLTP workloads. So, you can have memory-optimized tables, natively-compiled stored procedures, memory-optimized table variables, etc.

In-Memory OLTP offers a significant performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency.

In this series of articles, we will see in plain words, how we can take advantage of this technology and start using it for boosting performance of heavy workloads.

In this first article, we are going to see how easy is to create an In-Memory OLTP-enabled database and memory-optimized tables.

OK, enough talking, let's see some code!

--create database
CREATE DATABASE InMemOLTPDB;
GO  

--use the database

USE InMemOLTPDB;
GO

--add memory-optimized file group to the database

ALTER DATABASE InMemOLTPDB ADD FILEGROUP InMemOLTPDB_mofg CONTAINS MEMORY_OPTIMIZED_DATA;

--add file for memory-optimized objects

--you can set any path you like - in this example: c:\memoptdata
--if the directory does not exist you will need to create it
ALTER DATABASE InMemOLTPDB ADD FILE (name='InMemOLTPDB_mofg1', filename='C:\memoptdata\InMemOLTPDB_mofg1') TO FILEGROUP InMemOLTPDB_mofg;

--set the isolation level for memory-optimized tables to SNAPSHOT 

ALTER DATABASE InMemOLTPDB SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Now you can create memory-optimized tables.

When creating memory-optimized tables you have two options:
  • Option 1: You can create a durable memory-optimized table which means that in the case of server crash or failover the data will be available as they will be recovered from transaction logs.
  • Option 2: You can create a non-durable table which means that in the case of a server crash or failover the data in the table will be lost.

The below script creates a sample durable memory-optimized table:

--Memory-Optimized Table: Durable
CREATE TABLE [dbo].[Person_Durable]
  (
     ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL,
 Remarks VARCHAR(50) NOT NULL,  
  )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO


The below script creates a sample non-durable memory-optimized table:

--Memory-Optimized Table: Non-Durable
CREATE TABLE [dbo].[Person_Non_Durable]
  (
     ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL, 
 Remarks VARCHAR(50) NOT NULL, 
  )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

Stay tuned as in the next article we are going to run some simple experiments with In-Memory OLTP and get an indication on the performance benefits.

See also:

References

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

What are your views on the subject? Feel free to comment!

Thursday, January 5, 2017

Frequent Password Expiration: Time to Revise it?


Until recently, enabling the password expiration option in SQL Server was included in many security best practices. However, recent studies, revised this recommendation and support that it should not be further included in SQL Server’s security best practices. However, if this is the case, how can this recommendation be replaced with new, modern best practices that would take into consideration users’ behavior and habits?


Having the password expiration set to ON means that SQL Server checks for all logins that use SQL Server Authentication and contained database users with a password, if their password has expired, and if so, it prompts these logins and users to enter a new password.

Password expiration is part of SQL Server's Password Policy. SQL Server’s Password Policy can use Windows password policy mechanisms. Based on that, SQL Server can apply the same complexity and expiration policies just like in the case of Windows logins. SQL Server's security policy can be based either in Windows, on the database server (local security policy), or on the domain.

In the below example, I’m using the Local Security Policy MMC snap-in (secpol.msc) on my database server, in order to check the password policy settings.


As you can see from the above screenshot, the maximum password age is set to 90 days. This means that if I have a login that uses SQL Server Authentication or a contained database user with a password, if the login's or user's password was last changed 90 days (or more) ago, then the login/user will be prompted by SQL Server to change her password.

Even though this practice was used for many years, not only in SQL Server but similarly, on Windows-level, as well as in other systems and applications, recent studies argue that it should not be a recommended practice anymore. On the contrary, these studies suggest that user passwords should not be regularly changed but rather change only when there is a specific and justified reason to do so. Furthermore, new security standards are being formulated that contain new recommendations on password change.

The concept behind the need for new recommendations on password change, is that the whole process must be more user-friendly because as it is today, prompting the user to change her password every X days with no apparent reason, it only causes frustration to the user and has as an effect the user to set similar passwords on every password change or, set passwords that are easy to guess and this is the actual risk.

So, how can you apply this concept in action? How can you ensure to the maximum possible level that you have a healthy and robust password management policy in place? This can be achieved with a combination of actions and policies.

Examples of such practices could be the below:
  • Do not allow the user to set passwords that are easy to guess (you can use password dictionaries with easy-to-guess passwords for that).
  • Ensure that the minimum password length is set to 8 characters but do not limit the maximum length to a small number.
  • If applicable, present the user's in an easily readable way, when her last login took place so in order if she sees a strange login date/time to contact the administrator.
  • Monitor login auditing for "abnormal" login failures (i.e. consecutive login failures) and in this case prompt the user to change her password.
I'm sure that more sophisticated recommendations will be published on the subject along the way, as this is not really a new discussion but rather something that has been thoroughly discussed for years. I believe that now the time is mature to introduce flexible, new best practices regarding password management, that will eventually replace or improve old recommendations and offer a better security framework on the subject.



Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

What are your views on the subject? Feel free to comment!

Thursday, December 29, 2016

Selected Tweets of Year 2016!

Another year has passed...as always, full of everything! Events, blog posts, articles on well-known online SQL Server journals, software tools, and of course direct communication with you, my fellow SQL Server community members, via all available online and offline channels!

This year was very important for me. Besides my blogs and user group events, I have also authored many articles for well-respected online technology journals such as: MSSQLTips, SimpleTalk and SQLShack. Furthermore, I had the honor to be nominated by MSSQLTips committee for the "Rookie of the Year" contest.

Another major contribution was transforming my initiative "Artemiou Data Tools" to a more powerful initiative called "SQLArtBits". Via SQLArtBits I develop cool SQL Server tools and services which can help the user have an even richer experience with SQL Server and be more productive with less effort. The highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator.

Furthermore, I have transformed my personal blog from "The SQL Server and .NET Blog" to "The SQL Server and .NET Hub". The change in the name reflects also the change in the design. Now, via the new design you can easily search through hundreds of articles and find the SQL Server and .NET knowledge that you seek.

Last but not least, I have started a brand new initiative called "Tech How To's" via which I provide how-to's on just about any technology topic but mainly focusing on software such as: Databases, Programming, Windows, etc.

All the above, provided me with a great interaction with you, my fellow SQL Server community members, for one more year. Thank you for making the SQL Server community an amazing place to be! You rock!

I'm really looking forward for 2017 having already scheduled a variety of exciting community activities!

Wishing to all of you a Happy New Year 2017!

As every year's end, below you can find my selected tweets for the year 2016.


Blog Posts/Articles/Videos/Services/Books and Announcements

Dynamic Data Masking is one of the key #security features in #SQLServer 2016. Learn more at: http://www.sqlnethub.com/2016/04/sql-server-2016-dynamic-data-masking.html  #MVPBuzz

How to get started with #SQLServer http://www.techhowtos.com/databases/how-to-get-started-with-sql-server/  #HowTo

How To Show “This PC” Icon on #Windows 10 Desktop - http://www.techhowtos.com/2016/12/27/how-to-show-this-pc-icon-on-windows-10-desktop/  #Windows10

Temporal Tables in #SQLServer 2016 is a handy way for auditing data changes! http://www.sqlnethub.com/2015/12/sql-server-2016-temporal-tables.html

An amazing collection of #SQLServer tips by @SQLArtBits now available as a #free #download - Get it here! https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tips-collection-august-december-2016/

Have you checked Row-Level #Security in #SQLServer? http://www.sqlnethub.com/2016/02/sql-server-2016-row-level-security.html

In #DotNet, you can connect to and retrieve data from #SQLServer with just a few lines of code http://www.sqlnethub.com/2016/12/how-to-establish-simple-connection-from-csharp-to-sql-server.html  #MVPBuzz

[Blog] How to Establish a Simple Connection from a C# Program to SQL Server http://www.sqlnethub.com/2016/12/how-to-establish-simple-connection-from-csharp-to-sql-server.html  #SQLServer #DotNet #MVPBuzz

Have you checked Entity Framework: Code First? It's quite cool! http://www.sqlnethub.com/2016/04/entity-framework-code-first.html  #DotNet #SQLServer

What are exactly orphaned users in #SQLServer and how can we handle them? http://www.sqlnethub.com/2016/09/what-are-exactly-orphaned-users-in-sql-server.html

Weekly SQL Server Tip 17: The STRING_SPLIT Function in SQL Server 2016 https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-17/  #SQLServer #Tips

My latest article on @sqlnethub! This one is about .NET error handling #MVPBuzz
Cannot implicitly convert type 'string' to 'System.Windows .Forms.DataGridViewTextBoxColumn' - http://www.sqlnethub.com/2016/12/cannot-implicitly-convert-type-string-to-system-windows-forms-datagridviewtextboxcolumn.html  #DotNet

Article in which I'm suggesting some best practices for when patching a #SQLServer failover clustered instance http://www.sqlnethub.com/2016/12/how-to-patch-a-sql-server-failover-cluster.html

How to Patch a SQL Server Failover Cluster - http://www.sqlnethub.com/2016/12/how-to-patch-a-sql-server-failover-cluster.html  #SQLServer

Weekly #SQLServer Tip 16 with a little bit of #DotNet! https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-16/  #SQLArtBits #TipOfTheWeek

MSSQL Tiger Team Blog - SQL Server 2016 SP1: Know your limits - https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-sp1-know-your-limits/  #SQLServer

[Bog] How to Suppress the "N Row(s) Affected" Output Message in #SQLServer http://www.sqlnethub.com/2016/12/how-to-suppress-n-rows-updated-output-in-sql-server.html  #MVPBuzz

[Blog] Vote for me for the MSSQLTips Rookie of the Year Award! http://www.sqlnethub.com/2016/12/vote-for-me-for-mssqltips-rookie-of-the-year.html  #SQLServer #MVPBuzz #Community

[Blog] The timeout period elapsed prior to obtaining a connection from the pool - https://aartemiou.blogspot.com.cy/2016/12/timeout-period-elapsed-prior-obtaining-connection-from-the-pool.html  #MVPBuzz #DotNet

My blog is being transformed into a central hub of #SQLServer and .NET knowledge - Read the announcement! https://aartemiou.blogspot.com.cy/2016/12/the-sql-server-and-net-hub-answers-to-your-technical-questions.html  #MVPBuzz

Read my brand new article on @SQLShack and learn more about #SQLServer consolidation best practices! #MVPBuzz http://bit.ly/2gOzfPL

This week's #SQLServer tip by @SQLArtBits
Weekly #SQLServer Tip 15: T-SQL Enhancements & Other in SQL Server 2016 SP1 https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-15/   #SQLArtBits #TipOfTheWeek

Get your weekly bits of #SQLServer knowledge by @SQLArtBits #TipOfTheWeek series! This week's tip: Significant Changes in SQL Server 2016 SP1
Weekly #SQLServer Tip 14 - Significant Changes in SQL Server 2016 SP1 https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-14/  #SQLArtBits #TipOfTheWeek

[Blog] SQL Server 2016 SP1: Breakthrough Changes http://aartemiou.blogspot.com.cy/2016/11/sql-server-2016-sp1-breakthrough-changes.html  #SQLServer #MVPBuzz

[Blog] Updated: Main Limitations of #SQLServer Express Editions http://aartemiou.blogspot.com.cy/2016/10/main-limitations-of-sql-server-express.html  #SQLServer #MVPBuzz

[Blog] SQL Server 2016 SP1: A Pleasant Surprise - https://www.sqlartbits.com/blog/announcements/sql-server-2016-sp1-a-pleasant-surprise/  #SQLServer #SQLArtBits #performance

A brand new weekly #SQLServer tip by @SQLArtBits is out! This time is about #Security! #SQLArtBits
Weekly #SQLServer Tip 13: #Security Advice on Failed Login Attempts in SQL Server - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-13/  #SQLArtBits #TipOfTheWeek

[Video] Introducing DBA Security Advisor in 75 Seconds - https://youtu.be/xZ4KLvI8ago  #SQLArtBits #SQLServer #Security #Tools

This week's #SQLServer tip by @SQLArtBits is about durability options for memory-optimized tables in SQL Server #SQLArtBits #TipOfTheWeek
Weekly #SQLServer Tip 12 - Durability Options for Memory-Optimized Tables in SQL Server https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-12/  #SQLArtBits #TipOfTheWeek

[Video] Introducing In-Memory OLTP Simulator in 60 Seconds - https://www.youtube.com/watch?v=O_eT_7yYRME  #SQLServer #Software #InMemoryOLTP #Hekaton #SQLArtBits

[Blog] In-Memory OLTP Simulator v1.1 is Now Out! https://www.sqlartbits.com/blog/announcements/in-memory-oltp-simulator-v1-1-is-now-out/  #SQLServer #Software #Tools #InMemoryOLTP #Hekaton

Get backward compatibility info for any #SQLServer version with this #free online service: https://www.sqlartbits.com/services/sql-server-backward-compatibility-checker/  #mvpbuzz

Weekly #SQLServer Tip 11: T-SQL Code Snippets - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-11/  #SQLArtBits #TipOfTheWeek

My new article on @SQLShack is about T-SQL code snippets in #SQLServer and a special way of managing them! http://www.sqlshack.com/how-to-create-and-manage-t-sql-code-snippets/  #MVPBuzz

Check out my new article on @SQLShack! #SQLServer #MVPBuzz
How to create and manage T-SQL code snippets http://bit.ly/2eZBzyI  via @SQLShack w/ @artemakis

My latest article on @SQLShack is all about #SQLServer #Security. Check it out! http://www.sqlshack.com/securing-sql-server-surface-area/  #MVPBuzz

This week's #sqlserver tip by @SQLArtBits is all about #security!
Weekly #SQLServer Tip 10: Secure SQL Server https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-10/  #Security #InfoSec #TipOfTheWeek #SQLArtBits

New #Free online #SQLServer Service by @SQLArtBits: SQL Server Backward Compatibility Checker - https://www.sqlartbits.com/services/sql-server-backward-compatibility-checker/  #SQLArtBits

This week's tip is all about #SQLServer #security #infosec #mvpbuzz
Weekly #SQLServer Tip 9 - Password Expiration Option - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-9/  #SQLArtBits #TipOfTheWeek #Security #infosec

[Useful Table] Main Limitations of SQL Server Express Editions - http://aartemiou.blogspot.com.cy/2016/10/main-limitations-of-sql-server-express.html  #SQLServer #MVPBuzz

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'... https://www.sqlartbits.com/blog/sql-server-troubleshooting/sqlserveragent-could-not-be-started-reason-unable-to-connect-to-server-local/  #sqlserver #mvpbuzz

Top 10 security considerations for your SQL Server instances - http://www.sqlshack.com/top-10-security-considerations-sql-server-instances/  #SQLServer #MVPBuzz

Weekly #SQLServer Tip 8 by @SQLArtBits
Weekly #SQLServer Tip 8 is Out! https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-8/  #TipOfTheWeek #SQLArtBits

[Blog] Main Limitations of #SQLServer Express Editions - http://aartemiou.blogspot.com.cy/2016/10/main-limitations-of-sql-server-express.html  #SQL #MVPBuzz

Get your weekly #SQLServer tip! #TipOfTheWeek 7 - Query Store  https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-7/  #SQL #SQLArtBits

How to resolve the "cannot connect to WMI provider" issue - https://support.microsoft.com/en-us/kb/956013  A very helpful support article by #Microsoft #SQLServer

My new article on @mssqltips: Script to Manage #SQLServer Rebuilds and Reorganize for Index Fragmentation - https://www.mssqltips.com/sqlservertip/4470/script-to-manage-sql-server-rebuilds-and-reorganize-for-index-fragmentation/  #MVPBuzz

[Blog] What are Exactly Orphaned Users in #SQLServer? http://aartemiou.blogspot.com.cy/2016/09/what-are-exactly-orphaned-users-in-sql-server.html  #MVPBuzz

It's time for you weekly #SQLServer bits of knowledge by @SQLArtBits! Weekly SQL Server Tip 6 - Always Encrypted - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-6/

Retweeted @SQLArtBits:
[Blog] Why We Build SQL Server Tools - https://www.sqlartbits.com/blog/announcements/why-we-build-sql-server-tools/  #SQLServer #Software #Tools #SQL #MVPBuzz

Why we built In-Memory OLTP Simulator: https://www.sqlartbits.com/products/in-memory-oltp-simulator-ultimate/ #whywebuiltit  #MVPBuzz #SQLServer

My latest article on @mssqltips: Simple Experiment with #SQLServer In-Memory OLTP is 79 Times Faster! https://www.mssqltips.com/sqlservertip/4469/simple-experiment-with-sql-server-inmemory-oltp-is-79-times-faster/  #MVPBuzz #SQL

Get your weekly bits of #sqlserver knowledge! Tip of the week 5! #MVPBuzz
Ready for some bits of #sqlserver knowledge? #TipOfTheWeek 5: https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-5/  #SQLArtBits

Weekly #SQLServer Tip 4 - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-4/  #ArtDataTools #SQL #Tools

If you are just getting started with #InMemoryOLTP in #SQLServer you should read my article on #SimpleTalk https://www.simple-talk.com/sql/learn-sql-server/introducing-sql-server-in-memory-oltp/  #MVPBuzz

It's time for this week's #SQLServer Tip! https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-3/  #MVPBuzz #SQL

Easily get the latest service pack info for any #SQLServer instance with this #free online service - https://www.sqlartbits.com/services/sql-server-service-pack-info/  #mvpbuzz

My #free ebooks on #SQLServer have now over 6300 downloads! Learn more about SQL Server today! https://www.aartemiou.com/sqlbooks/  #SQL #MVPBuzz

Weekly #SQLServer Tip 2 - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-2/  #SQL #MVPBuzz

[Blog] The "Public" Database Role in SQL Server - http://aartemiou.blogspot.com.cy/2016/08/the-public-database-role-in-sql-server.html  #SQLServer #SQL #MVPBuzz

New #sqlserver #community initiative! Weekly SQL Server Tips! Tip 1 - https://www.sqlartbits.com/blog/weekly-sql-server-tips/weekly-sql-server-tip-1/  #community #mvpbuzz

[Blog] SQLServerAgent could not be started (reason: Unable to connect to server '(local)'... - https://www.sqlartbits.com/blog/sql-server-troubleshooting/sqlserveragent-could-not-be-started-reason-unable-to-connect-to-server-local/  #MVPBuzz #SQLServer

[Blog] Why Secure Your #SQLServer Instances? https://www.artdatatools.com/blog/sql-server-security/why-secure-sql-server-instances/  #MVPBuzz #Security

[Blog] Artemiou Data Tools: Free Online SQL Services - http://aartemiou.blogspot.com.cy/2016/08/free-sql-services.html  #MVPBuzz #SQLServer #Community #Software

My latest presentation on #SQLServer #Security: How to Secure Your SQL Server Instances - http://www.slideshare.net/artemakis/how-to-secure-your-sql-server-instances  #MVPBuzz

[Webcast] Learn how you can secure your #SQLServer instances by watching my latest #webcast - https://youtu.be/ZY96LtVSJbk  #Security #mvpbuzz

[Blog] Why You Need to Secure Your SQL Server Instances - http://aartemiou.blogspot.com.cy/2016/07/why-you-need-to-secure-your-sql-server.html  #SQLServer #Security #MVPBuzz

How can you export #SQLServer records into individual text files? Read my latest article on @mssqltips: https://www.mssqltips.com/sqlservertip/4353/export-sql-server-records-into-individual-text-files/  #MVPBuzz

[Blog] Operating System Requirements for all SQL Server Versions and Editions - http://aartemiou.blogspot.com.cy/2016/05/operating-system-requirements-for-all.html  #SQLServer #MVPBuzz

Learn how you can use #SQLServer Model system database. Read my my latest article on @mssqltips - https://www.mssqltips.com/sqlservertip/4265/modifying-the-sql-server-model-system-database-to-customize-new-database-settings/  #MVPBuzz

[Blog] Entity Framework: Code First - http://aartemiou.blogspot.com.cy/2016/04/entity-framework-code-first.html  #MVPBuzz #DataAccess

Learn how to implement #SQLServer In-Memory OLTP. Read my latest article on Simple-Talk Journal: https://www.simple-talk.com/sql/learn-sql-server/implementing-sql-server-in-memory-oltp/  #MVPBuzz #Hekaton

[Blog] SQL Server 2016: Dynamic Data Masking - http://aartemiou.blogspot.com.cy/2016/04/sql-server-2016-dynamic-data-masking.html  #MVPBuzz #SQLServer2016 #SQLServer #Security

[Blog] SQL Server 2016: The STRING_SPLIT Function - http://aartemiou.blogspot.com.cy/2016/03/sql-server-2016-stringsplit-function.html  #MVPBuzz #SQLServer

A must-read: Guidelines for Using Indexes on Memory-Optimized Tables - https://msdn.microsoft.com/en-us/library/dn133166.aspx  #Hekaton #SQLServer

Learn more about my experiment. Get the paper at: https://www.inmemoltpsim.com/publications/  #MVPBuzz #Hekaton #SQLServer

My article in @mssqltips: Assessing the SQL Server In-Memory OLTP Performance Benefits -https://www.mssqltips.com/sqlservertip/4201/assessing-the-sql-server-inmemory-oltp-performance-benefits/  #MVPBuzz #SQLServer #Hekaton

See how I achieved breakthrough performance (26x speedup) with #SQLServer #InMemoryOLTP! Get the paper at: https://www.inmemoltpsim.com/  #MVPBuzz

My technical article 'In-Memory OLTP Simulator:The Experiment' is now available for download! http://www.inmemoltpsim.com  #SQLServer #MVPBuzz

[Blog] SQL Server 2016: Row Level Security - http://aartemiou.blogspot.com.cy/2016/02/sql-server-2016-row-level-security.html  #MVPBuzz #SQLServer

My whitepaper titled 'In-Memory OLTP Simulator:The Experiment' to be published on March 2, 2016! http://www.inmemoltpsim.com  #SQLServer #MVPBuzz


Miscellaneous

Hi #sqlfamily! Follow my blog's twitter account @sqlnethub and stay up to date about my latest blog articles! Cheers, Artemakis.

Hi folks! Make sure you check out my blog which was recently transformed into something more powerful! http://www.sqlnethub.com  #SQLServer

Join our Facebook group and stay up to date with our latest articles and how-to's! https://www.facebook.com/groups/1563445467016000/  #SQLServer #DotNet #Community

Looking for a solution to a technical problem having to do with #SQLServer or #DotNet? Just run a search! http://www.sqlnethub.com/

The SQL Server and .NET Hub just got its brand new URL! http://www.sqlnethub.com  #MVPBuzz #SQLServer #DotNet #Community

Cool #free online service: #sqlserver supported Operating systems - https://www.sqlartbits.com/services/sql-server-supported-os/

Working on some new articles on #sqlserver #security, architecture and #performance via #InMemoryOLTP! #MVPBuzz

Check out our #free online #SQLServer services! https://www.sqlartbits.com/services/  #SQLArtBits #sql #community

Want to know fast which Operating Systems are supported by a #SQLServer version? Use this simple, #free #service - https://www.sqlartbits.com/services/sql-server-supported-os/

[News] Next week I'm starting a new series of technical journal articles which will be featuring interesting solutions! #SQLServer #MVPBuzz

Retweeted @SQLArtBits:
Too busy to visit our website? No problem, here's our rss feed: https://www.sqlartbits.com/rss/rss.xml  #SQLServer #Software #Tools

Retweeted SQLArtBits:
We are adding a special #benchmarking mode to In-Memory OLTP Simulator! More news soon! https://www.sqlartbits.com/products/in-memory-oltp-simulator-community/  #SQLServer #InMemoryOLTP

Retweeted SQLArtBits:
Make sure you check out our weekly #SQLServer tips for cool bits of #SQL knowledge! https://www.sqlartbits.com/blog/weekly-sql-server-tips/  #TipOfTheWeek #SQLArtBits

Retweeted @SQLArtBits:
All brand new Snippets Generator v3.1 to be released next Wed. Sep 21 2016! Check often: https://www.sqlartbits.com  #SQLServer #SQL #SQLArtBits

Retweeted @SQLArtBits:
Subscribe to our RSS feed and stay up to date with our latest news, releases and articles! https://www.sqlartbits.com/rss/rss.xml  #sql #sqlserver #software

DBA Security Advisor now on Facebook! https://www.facebook.com/dbasecadvisor/  #SQLServer #Security #Software #MVPBuzz

Easily test #SQLServer In-Memory OLTP with my software tool In-Memory OLTP Simulator! http://www.inmemoltpsim.com  #MVPBuzz

Another successful #CDNUG event took place! I hope you enjoyed my session on Entity Framework Code First! Thank you for attending! #MVPBuzz

Playing with Entity Framework Code First for my session in an upcoming event. A quite cool feature in the Data Access world! #MVPBuzz

Preparing my session for the upcoming #CDNUG event titled: Entity Framework: Lets Code First! #MVPBuzz #DotNet #SQLServer #DataAccess

Running hundreds of simulations against Hekaton via In-Memory OLTP Simulator! http://www.inmemoltpsim.com  #MVPBuzz #SQLServer

You really have to play with indexes against your workload in order to conclude on the best index type for your specific case. #SQLServer


News

Enjoy some real discounts by @SQLArtBits!
Who would like to see some real #holiday #discounts? Visit our store! 70% off on all our products! https://www.sqlartbits.com/store/  #SQLServer

Working on a new article about #SQLServer AlwaysOn Availability Groups. To be published tomorrow on my blog http://www.sqlnethub.com  #MVPBuzz

Proudly announcing: "The SQL Server and .NET Hub" - Your central hub for #SQLServer and .NET knowledge! https://aartemiou.blogspot.com.cy/  #MVPBuzz

Next week we will be publishing a brand new article about #SQLServer #Security - Check our blog often! https://www.sqlartbits.com/blog/  #SQLArtBits

[News] I'm in the process of transforming my main blog into something much much bigger! Stay tuned #SQLFamily! #SQLServer #MVPBuzz

In-Memory OLTP Simulator 1.1 (Build_20161120_1) on Softpedia - Read the review - Try it out! http://www.softpedia.com/get/Internet/Servers/Server-Tools/In-Memory-OLTP-Simulator.shtml  #SQLArtBits #SQLServer

The new build of In-Memory OLTP Simulator, made available today,fully supports #SQLServer 2016 with SP1! https://www.sqlartbits.com/products/in-memory-oltp-simulator-ultimate/  #SQLArtBits

Now everybody can experience the full power of #SQLServer with Server 2016 SP1! Many Enterprise features available! https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/

This is cool! https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/  #SQLServer

Coming up next week on Nov 1, with a cool new feature: Benchmark Mode #SQLServer #Hekaton #Software
SQLArtBits @SQLArtBits
[Announcement] In-Memory OLTP Simulator v1.1 will be released on Tuesday, Nov 1, 2016! Stay tuned! https://www.sqlartbits.com  #SQLServer

DBA Security Advisor v1.0 Build 20161020_1 is Now Out! #Secure your #SQLServer instances today! https://www.sqlartbits.com/products/dba-security-advisor-enterprise/  #security

So excited about this! #SQLServer #MVPBuzz
[News] Tomorrow, we are releasing a new build of DBA Security Advisor v1.0! https://www.sqlartbits.com  #SQLServer #Security #Tools #SQLArtBits

Deep diving into #SQLServer #security. Expect a series of some really interesting articles on security & hardening best practices! #MVPBuzz

Retweeted @SQLArtBits:
[News] A new build of our Enterprise product DBA Security Advisor is scheduled for release in the coming days. Stay tuned! #SQLServer #Tools

Do you like high-quality #SQLServer tools that just work? Do you enjoy simplicity? Check out @SQLArtBits: http://www.sqlartbits.com  #Software

Dynamic T-SQL Generator v2.0 is now out! It's #free and full of new features! Get it today! https://www.sqlartbits.com/products/dynamic-tsql-generator/  #SQLServer #MVPBuzz

[News] Dynamic T-SQL Generator v2.0 will be released on Wed October 5 2016! Stay tuned! https://www.sqlartbits.com/  #SQLServer #Free #Software

Coming up in mid-October: Dynamic T-SQL Generator v2.0 and it's full of brand new features! Check often: https://www.sqlartbits.com  #SQLServer

Snippets Generator v3.1 is now out and it's free! Get it at: https://www.sqlartbits.com/products/snippets-generator/  #MVPBuzz #SQLServer #SQL #Tools

Artemiou Data Tools is now @SQLArtBits! Some really cool stuff is coming up next week! https://www.sqlartbits.com  #mvpbuzz #community

A new build of DBA Security Advisor is available. See what's new at: https://www.dbasecadvisor.com/news/  #SQLServer #Security #MVPBuzz

Securing your #SQLServer instances has just been made easier: Check out my latest tool named 'DBA Security Advisor': https://www.dbasecadvisor.com

DBA Security Advisor is Now Out! Easily assess your #SQLServer instances against potential #security risks! https://www.dbasecadvisor.com/  #MVPBuzz

Working on my #free tool 'Snippets Generator' which supports #SQLServer 2012 or later. A major upgrade to be released this week! #MVPBuzz

I am a Microsoft Data Platform MVP for the 8th Year! http://aartemiou.blogspot.com.cy/2016/04/im-microsoft-data-platform-mvp-for-8th.html  #MVPBuzz #Community #SQLServer

Working on a new technical article on #SQLServer #InMemoryOLTP. More news about it next week! #Hekaton #MVPBuzz

New section on In-Memory OLTP Simulator's website: Publications https://www.inmemoltpsim.com/publications  #MVPBuzz #SQLServer #Hekaton



Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

Tuesday, December 20, 2016

How to Establish a Simple Connection from a C# Program to SQL Server

This is a basic example on how to establish a connection to SQL Server from a C# program.

Use the System.Data.SqlClient namespace in the top section of your code as below:
using System.Data.SqlClient;

Set the connection string - Trusted connection (Windows Authentication). In the below example I'm using "master" as the default database. You can replace "master" with the default database you wish to set:

string connString = @"Server=INSTANCE_NAME;Database=master;Trusted_Connection = True;";

In case you want to set a connection string with standard security (username/password - not recommended) then you can set the connection string based on the below example:

string connString = @"Server=INSTANCE_NAME;Database=master;User ID=USERNAME;Password=PASSWORD";

With the "using" keyword, .NET automatically manages the task of closing the connection to the database. This an easy way to avoid "connection leak" issues. Here's the code example:

 using (SqlConnection conn = new SqlConnection(connString))
            {
                //set the command to execute against SQL Server (this is where you set your query)
                string query = @"SELECT [fileid],[filename] FROM sysfiles";
                SqlCommand cmd = new SqlCommand(query, conn);

                //open connection
                conn.Open();

                //the actual command execution
                SqlDataReader dr = cmd.ExecuteReader();

                //if reader has any rows retrieve them
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        //handle the retrieved record (i.e. display it)
                        MessageBox.Show(dr.GetInt16(0) + " - " + dr.GetString(1));
                    }
                }
                else
                {
                    MessageBox.Show("No data found.");
                }
                dr.Close();
            }



Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

Wednesday, December 14, 2016

Cannot implicitly convert type 'string' to 'System.Windows.Forms.DataGridViewTextBoxColumn

If you get the above error message when developing a .NET application, then you have most probably set as a column name for one of your datagridview column the reserved word 'name'.

Example of Exception in Visual Studio when Using the Reserved Word "Name" as a DataGridView Column Name.

If you do this, then the compiler finds a conflict between the assignment of the form's name and the DataGridview's column name.

To resolve this issue just change the name of the DataGridView's column to something else instead of "name".

There is a reason there are reserved words not only in .NET byut in many other development platforms as well (i.e. SQL Server, etc.).

To this end, please make sure that you are not using any reserved words in your development work because if you use them, there is always the risk of a conflict with system routines like the above example.

Check this MSDN article for information about the words reserved by the linker.


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

For more articles click here!


Monday, December 12, 2016

How to Patch a SQL Server Failover Cluster

Patching SQL Server is one of the main administrative tasks in the DBA's life.

However, patching SQL Server is a procedure that needs to be planned, tested, and performed very carefully.

Below you can find a suggested methodology for patching a failover clustered instance of SQL Server. This example assumes a two-node failover cluster but can be easily extended to failover clusters with more nodes as the principle is the same.

1. Plan ahead, decide the patches to be installed
2. Check the patch requirements, check if there is enough disk space on the cluster nodes (especially on the drives the system databases and SQL Server binaries are located), check the consistency of all your SQL Server databases on the instances to be patched,

3. Apply the patch(es) on your Test environment by following the below steps:
3.1 Notify the affected parties. Get the required approvals.
3.2 Backup sytem databases: master, model, msdb
3.3 Backup the resource database (more info)
3.4 Backup user databases
3.5 Failover all SQL cluster roles to Node A (minimal downtime might occur during the switching - inform users)
3.6 Install the patches on Node B (passive)
3.7 Restart Node B
3.8 Failover all SQL cluster roles to Node B (minimal downtime might occur during the switching - inform users)
3.9 Install the patches on Node A (passive)
3.10 Restart Node A

4. Certify that everything works well on the Test failover cluster after the installation of patches is completed (perform tests with Node A active, then Node B active, etc.). You need to get acceptance by all affected parties (i.e. IT users, application owners, etc.).

5. If everything works well on the Test environment and you have the green light to proceed with patching Production, then you may follow the below steps (if not, then perform patch rollback on all cluster nodes and restore system databases):
5.1 Notify the affected parties
5.2 Backup sytem databases: master, model, msdb
5.3 Backup the resource database (more info)
6.4 Backup user databases
6.5 Failover all SQL cluster roles to Node A (minimal downtime might occur during the switching - inform users)
6.6 Install the patches on Node B (passive)
5.7 Restart Node B
5.8 Failover all SQL cluster roles to Node B (minimal downtime might occur during the switching - inform users)
5.9 Install the patches on Node A (passive)
5.10 Restart Node A

6. Certify that everything works well on the failover cluster after the installation of patches is completed (perform tests with Node A active, then Node B active, etc.). You need to get acceptance by all affected parties (i.e. IT users, application owners, etc.). If there are issues, perform patch rollback on all cluster nodes and restore system databases.

Note: Along with planning the patch testing and deployment, you need to get the required approvals as the above process might cause some downtime (i.e. during the failover actions).


Additional resources:

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)





Friday, December 9, 2016

How to Suppress the "N Row(s) Affected" Output Message in SQL Server

Sometimes, when working with T-SQL scripts, you might want to skip the message "(N row(s) affected)" message.

You get this message when you execute T-SQL statements or stored procedures that they affect rows. To this end, SQL Server returns the number of records which were affected by the database operation you performed.

If you want to suppress this message, then you can use the "SET NOCOUNT" statement.

So, for example you can try something like this:

SET NOCOUNT ON;

-- Your query goes here

SET NOCOUNT OFF;

Below, you can see an example with screenshots that illustrates the above


Figure 1: Running a T-SQL Statement with the Default NOCOUNT Setting.


Figure 2: Running a T-SQL Statement After Setting NOCOUNT to ON.




Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)



Thursday, December 8, 2016

Vote for me for the MSSQLTips Rookie of the Year Award!

I love being an active member of the SQL Server and .NET communities.

There is something magical in this.

I don't know. Maybe it's the thrill of writing a new article giving solution to a technical problem, or an article on a new technology or feature, or a webcast maybe?

Perhaps it's the excitement when presenting at a conference, or writing a new SQL Server tool in .NET.

All of that converge into a single thing: the interaction with you, my fellow technical community members.

After all these years of actively participating to the SQL Server and .NET technical communities, I believe that this is the key motivation in all this. The discussions, the feedback, both positive or negative, it doesn't really matter, the feeling that with your contributions you really help people, and so much more is the motivational power behind my contributions to the technical communities.

This year, among other, I started writing for the prestigious MSSQLTips online journal. MSSQLTips is one of the most established online SQL Server communities and with its founders’ professionalism as well as with all those high-quality tips, it helps hundreds of thousands, maybe even millions of fellow community members.

Every year, MSSQLTips runs the “Author of the Year” and “Rookie of the Year” contests. I had the honor to be nominated for the “Rookie of the Year” award this year.

So, if you enjoy my articles on MSSQLTips, I would appreciate it if you vote for me! This is very easy. You can just visit the MSSQLTips Author Voting page and vote!

As always, my commitment is that I will keep on contributing to the SQL Server community via all available online and offline channels.  I will keep on writing high-quality journal articles and blog posts. I will keep on writing cool SQL Server tools and books making them available to all of you. I will keep on presenting at conferences, user group events and webcasts! I will keep on tweeting...and the list goes on!

And yeah, you can definitely expect more tips in 2017! 


Cheers,
Artemakis


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

Tuesday, December 6, 2016

The SQL Server and .NET Hub: Answers to Your Technical Questions

As of today, The SQL Server and .NET Blog is The SQL Server and .NET Hub!

Why this change? Because this not just a blog anymore! It's being transformed into a hub with high-quality articles, how-to series, webcasts, and much more!

All the above have to do with different technical areas of SQL Server and .NET development (mainly C#).

So join us celebrating this transformation! Spread the news! Share this post! Join the Facebook Group! Let our fellow technical community members know about The SQL Server and .NET Hub!

P.S: We are in the process of also setting up a custom domain name for our in hub in order to make it easier for all to remember its URL.


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

The timeout period elapsed prior to obtaining a connection from the pool

If you are working with .NET applications, at some time you might get the below (or similar) error message:

Exception message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

The most probable reason you are getting the above error message, is that your application
opens connection to the database without closing them. This situation is then repeated and after a number of times, the application throws an exception because it cannot open more than a given number (max pool) of connections to the database.

The best practice is to let .NET do the connection handling for you by running your SQL queries with the "using" statement. Here's a C# example:

using (SqlConnection conn = new SqlConnection(connString))
   {
      SqlCommand cmd = new SqlCommand("SELECT @@VERSION", conn);
      conn.Open();      
      SqlDataReader dr = cmd.ExecuteReader();
      //process the result
      dr.Close();
   }
}

There is a good MSDN blog article about this (a little old but the principle is the same) which has more info.


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

Saturday, November 19, 2016

SQL Server 2016 SP1: Breakthrough Changes

A few days ago, Microsoft released Service Pack 1 (SP1) for SQL Server 2016. This release however, is not at all similar to older releases of SQL Server service packs, in a very pleasant manner.

In addition to cumulative patches, bug fixes, improvements, etc., SQL Server SP1 does something else as well; it makes features that until now you could only find them in Enterprise Editions, available to the rest of the Editions of SQL Server 2016 with SP1. Yes, you've heard right! This is an exciting, breakthrough change!

Now you can use In-Memory OLTP not only in the Enterprise Edition of SQL Server 2016 SP1 but also in the Standard Edition, Web Edition and even in the Express Edition! Also, you can use columnstore, dynamic data masking and much more!

Let's see below a table of Enterprise features of SQL Server 2016 that are now available in other editions as well, after of course upgrading to Service Pack 1:


Advanced Features of SQL Server 2016 SP1 Now Available in Other SQL Server 2016 SP1 Editions
Feature/SQL Server 2016 SP1 Edition SQL Server 2006 SP1 Enterprise Edition SQL Server 2006 SP1 Standard Edition SQL Server 2006 SP1 Web Edition SQL Server 2006 SP1 Express Edition SQL Server 2006 SP1 Local DB Edition
Row-Level Security
Dynamic Data Masking
Change Data Capture
Database Snapshot
Columnstore
Partitioning
Compression
In-Memory OLTP
Always Encrypted
PolyBase
Fine Grained Auditing
Multiple File Stream Containers
For more information please check this MSDN blog article.

As you can see this is indeed a breakthrough change. Now, everyone can experience the full potential of SQL Server. Now these features will be used even more broadly. New development work will include them even more thus creating new generation data applications that make use of these features. The possibilities are endless!

I'm planning to start a series of articles covering all these features in simple words and with simple examples so stay tuned!


Resources:


Read Also:



Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

Tuesday, October 4, 2016

Main Limitations of SQL Server Express Editions


SQL Server Express Editions are a handy solution for small businesses with small databases with no special requirements about performance, high availability, encryption, etc.

However, the Express Editions of SQL Server, even free, as expected, have certain limitations. In the below table I have gathered some of these limitations which I can consider are the limitations someone might want to check as a first look. In the end of each column, there is a URL with more information about all the limitations of the express edition for each SQL Server version.





Main Limitations of SQL Server Express Editions (note: this is not the full limitations list)
Feature/SQL Server Version SQL Server 2005 Express SQL Server 2008 R2 Express SQL Server 2012 Express SQL Server 2014 Express SQL Server 2016 SP1 Express
Maximum number of CPU/Cores 1 socket or 4 cores 1 socket or 4 cores 1 socket or 4 cores 1 socket or 4 cores 1 socket or 4 cores
Maximum memory per instance 1 GB 1 GB 1 GB 1 GB 1 GB
Maximum database size 4 GB 10 GB 10 GB 10 GB 10 GB
In-memory OLTP N/A N/A N/A No Yes
Always Encrypted N/A N/A N/A N/A Yes
Row-Level Security N/A N/A N/A N/A Yes
Dynamic Data Masking N/A N/A N/A N/A Yes
Partitioning No No No No Yes
AlwaysOn Failover Cluster Instances No No No No No
Always On Availability Groups N/A N/A No No No
Transparent database encryption N/A No No No No
In-Memory Columnstore N/A N/A No No Yes
Polybase N/A N/A N/A N/A Yes
URL for more info/full limitations list Click Here Click Here Click Here Click Here Click Here
For more articles please check: The SQL Server and .NET Blog Article by Artemakis Artemiou, Microsoft Data Platform MVP


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

*Note: The above table does not contain all limitations of SQL Server express editions. For the full lists of limitations, click on the URL provided in the end of each column for every SQL Server version.



Last Updated: Nov 19 2016




Tuesday, September 27, 2016

What are Exactly Orphaned Users in SQL Server

An orphaned user in SQL Server, is a user that exists in a database (Database-Security-Users) but for any reason, does not have a corresponding login in the instance's security (master database).

This can happen for different reasons.

One case, is backing up a database from one instance of SQL server and restoring it on another instance. When you do that, the corresponding login is not transferred to the new instance because it exists outside the database's scope. In this case you need to either create the login and map it to the database user or make use of the special stored procedure sp_change_users_login in order to create the database user-login association.

Another case would be deleting a SQL Server login (this is not a recommended action). This would leave the mapped database user orphaned.

You need to handle orphaned users. By just leaving them there you are actually maintaining a security risk because if a login is maliciously mapped to an orphaned database user, the login will inherit the orphaned user's permissions on the database.

There is however a case where you can have database users not mapped to SQL Server logins and this could be just fine. This case is when you make use of a Contained Database. Contained Databases are isolated from the instance and you can log into them by just using their users. This is the only case where you must not consider a database user as orphaned.


There is a SQL Server security tool developed by SQLArtBits and which is called DBA Security Advisor which scans your SQL Server instances for vulnerabilities and provides recommendations and remediation scripts/methods. One of those security checks, is checking for orphaned users in all the databases of all instances to be assessed.


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)


Saturday, August 27, 2016

The "Public" Database Role in SQL Server

In SQL Server, the Public database role in SQL server is a special database role that is given by default to all logins. You cannot remove a login's membership to this role because this behavior is built into SQL Server.










































But what this role does? Should we make use of it or at the end of the day just ignore it?

When you create a new login (and consequently a new database user) in SQL Server and do not give it any permissions anywhere (that is securable objects), then this database user inherits the permissions the public database role has.

So, let's see an example. On a SQL Server 2014 instance I have just created a login named 'testLogin' (not quite  unique, isn't it? :) and just granted access to the 'Adventureworks2014' database without doing anything else. As you can see from the below screenshot, my login was just granted the Public database role on the database.












































The question now is what permissions the 'testLogin' inherited on AdventureWorks2014? I have not provided any permission so it's just the permissions provided by the Public database role.

If we check the securableS for the Pulbic database role in AdventureWorks2014 security, we can see that the Public database role is granted with SELECT access to certain system catalog views such as:


  • sys.all_columns
  • sys.all_objects
  • sys.all_parameters
  • sys.all_sql_modules
  • sys.all_views
  • sys.allocation_units
  • sys.assemblies
  • etc.



















































Now here comes the security recommendation:


Even though the Public database role is granted by default SELECT permissions to certain system catalog views, you should never, ever add more permissions to this role. Just leave it as it is. If you make the mistake and add more permissions to the public database role then this will mean that any login that will be granted access to the database it will inherit all these permissions. So please, don't do that!

Let's try again to rephrase this with even more plain words: any permissions the Public database role has, are automatically granted to all database users. That's why you should never add permissions to this role.

By the way, if you want to check if you have accidentally added more permissions to this role, you can try the below query:

USE [Database Name];
GO

SELECT 
DB_NAME() as DBName,
pm.[permission_name] as PermissionName,
ob.[name] as ObjectName,
pm.class_desc as ObjectType,
ob.type_desc as TypeDescription
FROM sys.database_permissions pm
INNER JOIN sys.database_principals pr ON pm.grantee_principal_id= pr.principal_id
LEFT JOIN sys.objects ob ON pm.[major_id] = ob.[object_id]
WHERE [state]='G' and major_id>=0 and pr.[name]='public' and ob.[name] IS NOT NULL;
GO

If the above query return any results, you should revise the permissions granted to the Public database role for the specific database.

Useful Information
I have recently released a brand new software tool called "DBA Security Advisor" which checks SQL Server instances for vulnerabilities using a large number of security checks. Among other, it checks if the Public database role has been granted additional permissions besides the defaults. Check it out here!




Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)


Featured Documents

SQL Server Development

View more

SQL Server Administration

View more

SQL Server Troubleshooting

View more

.NET

View more

SQL Server Performance

View more

SQL Server Programmability

View more

SQL Server Security

View more