Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,846 hits

  • Select GETDATE()

    March 2014
    M T W T F S S
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
    31  

Archive for March, 2014

« Previous Entries

SQL 2014 Learning Series # 1 – New Permission – Connect any database

Posted by blakhani on March 27, 2014


Finally, I was able to get the RTM bits of SQL Server 2014 and installed it on my laptop. Since there are lots of new things, I thought of sharing this with community. I will write them under “SQL 2014 Learning Series”

First thing first.. Here is the @@version for SQL 2014 on my laptop.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

 

After installing SQL Server 2014, I wanted to check what’s newly added in security layer of SQL Server. So I compared the permission available under server level and found three new permissions in SQL Server 2014

image

My default instance is SQL 2012 and I can see 31 permission whereas SQL 2014 has 34 permissions. These permissions are listed under Server properties >  permissions tab.

image

Here is the list

  • CONNECT ANY DATABASE
  • IMPERSONATE ANY LOGIN
  • SELECT ALL USER SECURABLES.

In this blog, we would see use of “Connect any database” . Let me create a SQL login Balmukund on my SQL Instance. Here is the command I have used

USE [master]
GO
CREATE LOGIN [Balmukund] WITH PASSWORD=N'@Very$tr0ngP@$$w0rd'
GO

 

Now, If I login with the account and try to expand user database, I will get the error.

image

This is what we have seen in earlier version of SQL as well. Unless we add user to the database, it would not be possible to expand database.

Let’s give new permission to that login. 

use [master]
GO
GRANT CONNECT ANY DATABASE TO [Balmukund]
GO

Once permission is given, Balmukund should be able to expand database. But wait.. can he see all objects as well?

image

Balmukund is unable to see the objects. This is also mentioned in books online “Does not grant any permission in any database beyond connect”

You may ask… what’s the use? All we are avoiding is just the error message? Well, there have been ask from customers to provide “Read-Only” or “Auditor” role for SQL Server. Combining this with other new permission can help in achieving that. The cool thing about this permission is that it will also allow logins to connect to databases that would be created in the future.

In next blog, I will show you demo about other new permissions.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
    • Advertisement

    Posted in SQL 2014 Learning Series, SQL Server, SQL Server 2014 | Tagged: , , , , , | 4 Comments »

    UDL Test Series – Part 2 – Easiest way to create connection string

    Posted by blakhani on March 20, 2014


    In the last post (part 1) we have discussed about how to create UDL and how a basic connectivity check can be performed. Another cool thing about UDL is that it can help a developer to create connecting string for the selected provider. Here are the steps.

    • Create a blank UDL file (Refer Part 1).
    • On the first tab, choose provider. I have selected “SQL Server Native Client 11.0”

    image

    • Hit Next or move to next tab “Connection” and provide necessary values. I have given server name (SRV3), selected “Use windows NT integrate security” and typed in database name as tempdb.

    image

    • On “Advanced” tab, I have entered timeout as 60 (I have given some random number for demo)

    image

    • On last tab “All”, we can edit rest of the parameter which can be provided in connecting sting. I have used “Application Name” in below screenshot and edited it to Balmukund

    image

    • Once all necessary values are entered. Hit OK.
    • Now, open the file with “notepad” and have a look (this is the key of the trick because double click would open properties again)

    image

    Notice that connection string is ready and can be used in the application code. Interesting? Please comment if it was useful.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Troubleshooting | Tagged: , , , | Leave a Comment »

    UDL Test Series – Part 1 – A Basic Connectivity Check

    Posted by blakhani on March 18, 2014


    In most of the situations you may not have SQL Server Management Studio (SSMS), command line tools (SQLCmd, OSql, Isql) to check whether you are able to connect from given client to SQL Server or not. If you have ever called Microsoft SQL Support team for connectivity issue, “UDL Test” would be familiar to you.

    UDL stands for Universal Data Link. Its “universal” means it is not just to test SQL Server connectivity test, but it works for other RDBMS as well. We can also get connection string using UDL file. Lets start with connectivity first.

    To create UDL file, Right Click anywhere on desktop > New > Create a empty Text Document file.

    image

    Once you have text file, change the extension of the file to udl. A warning might appear, explaining that changing file extensions could cause files to become unusable, hit OK. In case you don’t see file extension: Open Windows Explorer, and on the Tools menu, click Folder Options. On the View tab, clear the Hide file extensions for known file types check box and then click OK.

    As soon as you would change the extension icon would change as below.

    image

    Now, double click on the file and on the first tab “Provider”, choose appropriate provider. Since I am going to test connectivity to SQL, I have used “SQL Server Native Client 11.0” provider and hit “next”

    image

    Next tab is “Connection” which is about the login credentials. Think of the SSMS login screen and it has all the details asked. We can choose Windows/SQL Login. Everything is self-explanatory.

    image

    After choosing three settings, we can do a “test connection” and see whether this client is able to connect to SQL Server or not. In case of problem, correct error from native client would be thrown.

    This is one of the test which every troubleshooter should know.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Troubleshooting | Tagged: , , , | 2 Comments »

    « Previous Entries