Admin: Bulkadmin vs ADMINISTER BULK OPERATIONS

I had an application thrust upon me recently which required the user executing it to have permissions to use the BULK INSERT command in T-SQL. I was aware of the server role bulkadmin which would have been a nice sledgehammer approach to crack this nut, but I was hoping for something a little more refined. Unfortunately, the only thing I came up with was the ADMINISTER BULK OPERATIONS command which would grant this privilege without having the server role membership but I was curious as to what the difference between the 2 was.

As far as I can tell, there is no difference between the 2 operations and both seem to achieve the same thing. From this I assume that bulkadmin is just a wrapper around the ADMINISTER feature although I'd be interested in knowing (as I couldn't find out) if there are other permissions the server role gives you.

Lets look at the example (running on SQL2008 SP2)

-- create some test logins
CREATE LOGIN [DOMAIN\user1] FROM WINDOWS
GO
CREATE LOGIN [DOMAIN\user2] FROM WINDOWS
GO
-- lets impersonate a user
EXECUTE AS LOGIN = 'BARRHIBB\user1'
GO
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO 
-- does the user have bulk op permissions? 
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO

-- lets go back to being a superuser 
REVERT
GO
USE MASTER
GO
-- how about if we just grant administer bulk operations to the user? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO
-- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO



-- can the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO

Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

So lets see what happens with the server role:

REVERT
-- remove the users permissions 
REVOKE ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO 
-- now add the user to the bulk admin role and check permissions again
EXEC MASTER..sp_addsrvrolemember @loginame = N'DOMAIN\user1', @rolename = N'bulkadmin'
GO
 -- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER') 
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO

NB: The main difference here is that the permission has not been explicitly granted but it is an effective permission.

-- perhaps with the server role the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO

Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

So it seems that the bulkadmin server role doesn't offer any "admin" from a security point of view which begs the question, just why would you add a user to this role rather than just explicity grant them the permission through the GRANT statement (or vice versa)?

Visual Studio: Change default browser

When debugging web apps in Visual Studio, the browser it throws up is typically your default browser. Makes sense I guess. However, once you have many browsers on your machine you may wish to change this.

This is the scenario I cam up against the other day and thanks to this article, I managed to do find a way.

Essentially, you need to right click on an ASP.NET (.aspx) page in your project, right click and select "Browse With..." and change the default browser.

 

NB: if you are developing an MVC with Razor, I found you had to just add an .aspx file to the project to achieve this goal then remove it.

T-SQL: Find missing gaps in data

I recently had solve a problem of finding missing values in a data series and at the same time, came across this article on the new Analytic Functions in SQL2012.

It turns out that there is a new function which can simplify my solution although as the problem is on a SQL2008 database, I won't be able to implement it. Still, another piece of the jigsaw.

Here, I'll present queries for each version:

USE TempDB
GO 

-- setup the table 
CREATE TABLE [dbo].[HoleyTable](
   [Date] [datetime] NOT NULL,
   [Country] [char](3) NOT NULL,
   [Val] [decimal](22, 10) NULL
) ON [PRIMARY]
GO

 -- and some sample data
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100131','GBP', 40) 
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100331','GBP', 30) 
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100531','GBP', 20)
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20101031','GBP', 50) 
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20101231','GBP', 55) 
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100228','USD', 20)
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100331','USD', 10)
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100430','USD', 15) 
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100630','USD', 25)
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20100731','USD', 55) 
INSERT INTO dbo.HoleyTable (Date, Country, Val) VALUES ('20101130','USD', 30) 
GO 

-- SQL2008
WITH RankedData 
AS
( 
SELECT *, ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Date) AS rn 
FROM dbo.HoleyTable 
) 
SELECT a.Country,
   b.Date AS StartOfGap,
   a.Date AS EndOfGap,
   DATEDIFF(m, b.date, a.date)-1 AS missingdatapoints 
FROM RankedData a
   INNER JOIN RankedData b
       ON a.rn = b.rn + 1
           AND a.Country = b.Country 
WHERE DATEDIFF(m, b.date, a.date) > 1; 
GO

-- SQL2012
WITH PeekAtData  
AS
(
SELECT *,
       curr = date,
       nxt = LEAD(Date, 1, NULL) OVER (PARTITION BY Country ORDER BY date)FROM dbo.HoleyTable 
)
SELECT Country,
      curr AS StartOfGap,
       nxt AS EndOfGap,
   DATEDIFF(m, curr, nxt) -1 AS MissingDatapoints 
FROM PeekAtData p
WHERE DATEDIFF(m, curr, nxt) > 1
GO 

--tidy up
DROP TABLE dbo.HoleyTable
GO


And how about the all important question of performance? Well, here is a screenshot of the execution plans for the 2 queries where you can see the 2012 implementation does outperform its 2008 counterpart by a ratio of about 3:1.

What is the RIGHT technology?

One of things I strongly believe in is using the right technology for solving the right problem. You can often find (understandably) that IT professionals look for solutions using technologies they understand best which can lead to inappropriate decsions being taken. "When all you have is a hammer....".

However, just how do you define the right technology?

An analogy. Whats the best way of buying music? Is it online or in HMV (other retail outlets are available)? Now, for price and convenience then online is pretty tough to beat. But what if you don't have a computer or don't know how to use one? There may be a premium associated with buying in the high street but its still the best understood method there is - everyone knows how to buy from a shop.

I've been involved with a particular application written entirely on SQL Server technologies and its an impressive implementation. Its fast and reliable and for someone familiar with the technology entirely supportable. However, its not me that has to use it. The "customer" has limited technology skills and yet is tasked with driving the application with recurring difficulty and as a consequence, there is a feeling that the application is not robust. The fact is, that the application is entirely robust, but is not built for a technology-light person to use.

Previous to the SQL implementation, there was an Excel equivalent which was less robust and performant. However, the users fully understood how to work it and were much more comfortable with it even at the cost of longer runtime. Asked to use its Excel predecessor, I would have probably claimed it not robust as a result of not being comfortable using that technology. Same issue, different technology.

We need to make the right technology choices and this doesn't just mean what is the best technology for the job. First and foremost, applications need to be fit for purpose and usable by the customer even if thats at the expense of the most elegant technology solution. If customers have no experience or understanding of a particular technology, then we shouldn't be building solutions that require that skillset. Simple.

Admin: Start and Stop Local Instance of SQL Server

As a database professional, i'm accustomed to having a local sql server instance installed. However, it seems that SQL Server (typically Express) is finding its way onto more and more desktops, often unused and unknown to the user!! With the web browser FireFox taking up almost half your memory, you don't want any other applications taking your valuable resources.

I recommend changing the service settings of your SQL services to manual and only turning them on when you are actually going to use them. If you're using a shared database server, you may find that even as a SQL developer you may not use your local instance as often as you might have thought.

I have created 2 simple batch files on my desktop which I can use for stopping and starting my local SQL services meaning its a mere double click away to get your instance available.

StartSQLServices.bat

NET START mssqlserver
NET START sqlserveragent
NET START ReportServer
NET START msdtsserver100

StopSQLServices.bat

NET STOP mssqlserver
NET STOP sqlserveragent
NET STOP ReportServer
NET STOP msdtsserver100

* your Service names may vary depending on instance names and SQL version.