Sunday, October 29, 2017

SQL Train Experience

Sorry for being late with this post.
Just have to recap from the PASS Summit 2017.

Here I'll briefly talk about SQL Train Experience.

The organizer of the event Chris Hyde has already published a blog about it I'm just want to post my pictures and briefly describe everything I think worth to mention.

At first: Frequently Asked Questions.

Q: What is a SQL Train?
A: SQL Train is a small event organized by Portland SQL enthusiasts and it happens in an actual train moving from Portland, OR to Seattle, WA. The whole cart is just full of SQL pros, donuts and refreshments.

Q: When that event takes place?
A: The event is usually scheduled exactly between SQL Saturday Oregon (www.SQLSaturday.com) and PASS Summit (www.PASS.org) in Seattle, WA.

Q: Is it always scheduled like this?
A: No, but it will continue until PASS Summit will take place in Seattle, WA.

Q: Can you tell more about timing?
A: Train departures from Portland, OR at about 12 P.M. and arrives to Seattle, WA approximately 4 P.M.

Q: Where and when do we gather for the event?
A: Union Station 800 Northwest 6th Avenue Portland, OR 97209. One hour before train departure (about 11 A.M.) It is also possible to board the train at Vancouver, WA train station, but you will miss the boarding experience.

Q: Who is participating in that event?
A: Anybody who purchased a ticket for that event. The most of the participants are attendees of SQL Saturday in Portland, OR and they are moving towards PASS Summit conference. A lot of participants are SQL Saturday speakers, PASS Summit speakers, SQL Saturday and PASS Summit volunteers and just regular SQL professionals joining wonderful #SQLFamily.

Q: How many people are participating in the event?
A: This time we had a full cart with 36 people. It is possible that next year we can have two carts. It is up to you if we can fill two carts.

Q: What kind of experience I can expect in SQL Train?
A: You will be surrounded by SQL gurus and you can ask any question regarding SQL and get it answered by a gang of high level pros. You can hear stories from around the world, about other SQL events and what caveats they have. You can make new friends to hook up with them for the entire week of following PASS Summit. Donuts and refreshments are part of the ticket price. You won't be hungry or thirsty. You can use train's Wi-Fi to do some work or to tweet about the event.

Q: What kind of food drinks are served on board?
A: For food there are only donuts and power bars along with beer and white/red wine. If you have special preferences then better bring your own. Organizers will not be able to fit all 70 people's requirements.

Q: Will I see any scenic views from the train?
A: It depends. In our case it was foggy and we see almost nothing, but that is not what you board SQL train for.

Q: When will be the next SQL Train?
A: Next event will take a place at  November 4th of 2018. Please plan ahead.

Q: Where can I purchase SQL Train ticket?
A: At first, monitor Twitter hash tag #SQLTrain. Second, contact Chris Hyde and let him know you are interested to include you into a mail list.

Q: When should I purchase the tickets?
A: As soon as sale starts. The number of seats is very limited.

Q: How much ticket costs?
A: This year it was $40, but next year it might slidely change in any direction.

Q: Can I bring my spouse/partner/girlfriend/boyfriend on board with me?
A: Absolutely. Just do not forget to purchase a ticket in time.

Q: I have a big luggage. Will it fit into train?
A: The best way to manage a luggage is to give it to train attendant in Portland and get it back in Seattle. Like you do it in airport. Airports just faster.

Q: May our company sponsor the event?
A: Please ask organizers, but why not? That is wonderful business opportunity. If you will provide better travel experience for two carts of SQL professionals from around the World you can gain their attention for the whole four hours.

Sorry of that QA is too messy and if I did not cover something please ask me or directly the organizer of the event Chris Hyde.

And now some photos:

At first we gathered at the train station:



Train station is very old you can see a lot of empty telephone boots, but sign is still functioning.

Just got to the train.

Already excited.

Too much to eat and drink.

First tweets from the #SQLTrain !




Polishing presentation, but tweets are more important

People prefer not to sit, but stay, walk and chat.















Tuesday, September 5, 2017

Giving special rights for group or user

SQL Server provides great flexibility for different types of security solutions.
In this post I want to show one of them.

Problem description:
1. Need to create a group/user "User1", which has to have only CRUD (Create-Read-Update-Delete) permissions for data in schema called "Schema1".
2. Need to create a group/user "User2", which has to have similar permissions as "User1" and have to be able create Views/Procedures/Functions in schema called "Schema2".
3. The group/user "User1" has to have Select/Execute permissions for all newly created objects in "Schema2".

Solution: Create a special database role for group/user "User2".

Here is how it can be done:

Preparation:

/* Create Test database */
use master;
GO
CREATE DATABASE TestPermissionDB;
GO
ALTER DATABASE [TestPermissionDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT;
GO
USE TestPermissionDB;
GO

/* Create Test Schemas */
CREATE SCHEMA Schema1;
GO 
CREATE SCHEMA Schema2;
GO 
/* Create Test Users and give them CRUD permissions*/
CREATE USER User1 WITH PASSWORD=N'39+pjFkM6+9ll131C9RBWIYL4KcaFSHIqYwK16+B9ec=';
GO
CREATE USER User2 WITH PASSWORD=N'sw/Efa4VCM6bUrz5s+jl8zuRas5r6U8IP8eiUO83NTk=';
GO
ALTER ROLE [db_datareader] ADD MEMBER User1;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User1;
GO
ALTER ROLE [db_datareader] ADD MEMBER User2;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User2;
GO
/* Create Test table */
SELECT 'ABC' as Sample_Data
INTO Schema1.tbl_Sample_Table;
GO

Give special permissions for User1:

/* Grant Permissions for User1 */
GRANT EXECUTE TO User1;
GO

Create special Role:

/* Create special test role and give it specific permissions */
CREATE ROLE SpecialRole;
GO
GRANT CREATE PROCEDURE to SpecialRole;
GO
GRANT CREATE VIEW to SpecialRole;
GO
GRANT CREATE FUNCTION to SpecialRole;
GO

And here is the magic.
/* Associate special test role with Schema2 and assign User2 to that role */
ALTER AUTHORIZATION ON SCHEMA::Schema2 TO SpecialRole;
GO
ALTER ROLE SpecialRole ADD MEMBER User2;
GO

From this point User2 has all permissions it needs.

Test User2 Permissions:

/* test Permissions for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE FUNCTION Schema2.fn_Test() RETURNS CHAR(3) as 
BEGIN
RETURN (SELECT TOP 1 Sample_Data FROM Schema1.tbl_Sample_Table)
END;
GO  
CREATE VIEW Schema2.vw_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE Schema2.sp_Test @VAL CHAR(3) as 
SELECT * FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = @VAL;
GO  
REVERT ;  
GO 
Everything should execute successfully.

Now will test restrictions for User2:
/* test Permission failures for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE VIEW Schema1.vw_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE dbo.sp_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
REVERT ;   
It should return following errors:
Msg 2760, Level 16, State 1, Procedure vw_Failed_Test, Line 1 [Batch Start Line 92]
The specified schema name "Schema1" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure sp_Failed_Test, Line 1 [Batch Start Line 95]
The specified schema name "dbo" either does not exist or you do not have permission to use it.

Test permissions for User1:

 /* test Permissions for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
INSERT INTO Schema1.tbl_Sample_Table VALUES ('XYZ');
GO
EXEC Schema2.sp_Test 'XYZ';
GO
UPDATE Schema1.tbl_Sample_Table 
SET Sample_Data = '123' 
WHERE Sample_Data = 'XYZ';
GO
SELECT * FROM Schema2.vw_Test;
GO
DELETE FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = '123';
GO
SELECT Schema2.fn_Test() as Function_Result;
GO
REVERT ;  
GO
It should return no errors:
If we try to use User1 to create an object like this:
/* test Permission failures for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
CREATE PROCEDURE Schema2.sp_Failed_Test as SELECT 1 as ABC;
GO
REVERT ;  
GO
Will get an error:
Msg 262, Level 14, State 18, Procedure sp_Failed_Test, Line 1 [Batch Start Line 130]
CREATE PROCEDURE permission denied in database 'TestPermissionDB'.

Do not forget to drop test database at the end:
/* Drop Test database */
use master;
GO
DROP DATABASE TestPermissionDB;
GO

Tuesday, August 8, 2017

Drawing SQL Sphere

<= Drawing 3D Cube

Couple of years ago I came up with an algorithm of drawing an ellipse using SQL Server spatial geometry: http://slavasql.blogspot.com/2015/02/drawing-ellipse-in-ssms.html

I've used that algorithm to make a sphere and as in my previous blog of drawing 3D Cube I use external procedure to simplify the process.
This time instead of temporary stored procedure I'm using a function to generate Geometrical content.
Here is the function's code:
SET NOCOUNT ON
GO
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Elipse') IS NOT NULL
DROP FUNCTION dbo.Elipse
GO
CREATE FUNCTION dbo.Elipse(
@x FLOAT = 0,   -- x coordinate of center
@y FLOAT = 0,   -- y coordinate of center
@rh FLOAT = 1, -- Horizontal radius
@rv FLOAT = 0, -- Vertical Radius
@as FLOAT = 0, -- Starting angle
@af FLOAT = 0 -- Finishing angle
) RETURNS GEOMETRY AS
BEGIN
SELECT @af = CASE @af WHEN 0 THEN 2*Pi() ELSE @af END
 , @rv = CASE @rv WHEN 0 THEN @rh ELSE @rv END

DECLARE @MP VARCHAR(MAX)='';
-- angle that will be increased each loop
DECLARE @theta FLOAT = @as;
-- amount to add to theta each time (degrees)
DECLARE @step FLOAT = 2 * PI()/1000;  
DECLARE @x1 FLOAT, @y1 FLOAT
, @x2 FLOAT = @x + @rh * SIN(@theta)
, @y2 FLOAT = @y + @rv * COS(@theta);

WHILE @theta <= @af
SELECT 
    @x1 = @x2, @y1 = @y2,
    @x2 = @x + @rh * SIN(@theta), 
    @y2 = @y + @rv * COS(@theta), 
    @theta += @step,
 @MP = @MP + '(' 
        + CAST(@x1 as VARCHAR) +  ' ' 
        + CAST(@y1 as VARCHAR) + ',' 
        + CAST(@x2 as VARCHAR) +  ' ' 
        + CAST(@y2 as VARCHAR) + '),';

RETURN (CONVERT(GEOMETRY,'MULTILINESTRING(' 
        + LEFT(@MP,LEN(@MP)-1) + ')')).MakeValid();
END
GO

The easiest way to use that function is to run a SELECT statement of it with some parameters:
SELECT tempdb.dbo.Elipse (10, 10, 1, .5, default, default).STBuffer(0.002);
If you decide to not specify the fourth parameter (@rv - Vertical Radius) you'll get a perfect circle!
SELECT tempdb.dbo.Elipse (0,0,1,default,default,default).STBuffer(0.005);
If you will specify starting and finishing angles you can get a part of an ellipse like this:
SELECT tempdb.dbo.Elipse (10,10,1,0.5,Pi()/4,5*Pi()/4).STBuffer(0.01);
And finally, if you combine several ellipses into one picture you can get a sphere:
/*
Make sphere with Axial tilt 23.437%
*/
DECLARE @d FLOAT = RADIANS(23.437), /*Axial tilt*/
@LR FLOAT = Pi()/4, /* Rotation LEft-Right*/
@x FLOAT = 0, /*Coordinate X*/
@y FLOAT = 0, /*Coordinate Y*/
@r FLOAT = 1, /*Radius*/
@w FLOAT = 0.001  /*Line width*/

DECLARE @g TABLE (g GEOMETRY);

-- Main circle
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (@x, @y, @r, default, default, default )

-- North Pole
INSERT INTO @g SELECT CONVERT(GEOMETRY,'POINT('
       +CAST(@x as VARCHAR)+' '+CAST(@y + @r * COS(@d) as VARCHAR)
       +')').STBuffer(@w)

-- Draw Equator:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (@x, @y, @r, @r * SIN(@d), Pi()/2, 3*Pi()/2)

-- Draw 30 degrees north:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y + @r * COS(Pi()/6)*COS(@d), 
 @r*SIN(Pi()/6), 
 @r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
 ACOS( TAN(@d) / TAN(Pi()/6) )
 , 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/6)  ))

-- Draw 60 degrees north:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y + @r * COS(Pi()/3)*COS(@d), 
 @r*SIN(Pi()/3), 
 @r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
 ACOS( TAN(@d) / TAN(Pi()/3) )
 , 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 60 degrees South:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y - @r * COS(Pi()/3)*COS(@d), 
 @r*SIN(Pi()/3), 
 @r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
 Pi()-ACOS( TAN(@d) / TAN(Pi()/3) )
 , Pi()+ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 30 degrees south:
INSERT INTO @g 
SELECT tempdb.dbo.Elipse (
 @x, @y - @r * COS(Pi()/6)*COS(@d), 
 @r*SIN(Pi()/6), 
 @r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
 Pi()-ACOS( TAN(@d) / TAN(Pi()/6) )
 , Pi()+ACOS( TAN(@d) / TAN(Pi()/6)  ))

SELECT g.STBuffer(0.005) FROM @g
GO

That sphere is supposed to represent the Earth looking at the Sun at Summer Solstice.