Yesterday I went to Gromada Hotel to see Steve Ballmer's (CEO of Microsoft Corp.) speech (special meeting with MVP community). Steve was asked about MSN (comparing to Google), opening devs center in Poland, and Vista improvements. He is indeed a great speaker. Of course we know him as very impulsive person, but this time his performance was slightly different. I was about to ask him about SQL Server 2005 SP3, but there was no time for more questions :-) Anyway, this is deffinitely a good news that VIPs from Microsoft remember and care about Polish communities.

If you have tried to use is_srvrolemember system function (which allows to check if login is a member of chosen fixed server role) then you know there are some problems with it. If you have a Windows login which is not trusted explicitly in SQL Server then is_srvrolemember returns NULL no matter which fixed role will be entered as function parameter. Couple days ago I started to think about writing my own is_srvrolemember implementation. The main problem is that the system stored procedure xp_logininfo can't be executed explicitly inside the function code. So, finally I've decided to write a stored procedure. The procedure is shown below. It takes two input parameters (fixed server role name and the name of login of any type - Windows or SQL Server), checks if a given login is in a given role and returns: 0 - if login is not a member of role, 1 - if login is a member of role, 2 - if login or role does not exist or if SQL Server can't get information about Windows login from DC.

USE master
GO

IF OBJECT_ID(N'dbo.sp_logininfo') IS NOT NULL 
    DROP PROCEDURE dbo.sp_logininfo
GO

CREATE PROCEDURE dbo.sp_logininfo
    (
      @role SYSNAME,
      @login SYSNAME
    )
AS 
    SET NOCOUNT ON
    DECLARE @result TINYINT
    DECLARE @login_info TABLE
        (
          account_name SYSNAME NULL,
          [type] CHAR(8) NULL,
          privilege CHAR(9) NULL,
          mapped_login_name SYSNAME NULL,
          permission_path SYSNAME NULL
        )
        
    SET @result = 0
    
    BEGIN TRY 
        INSERT  @login_info
                EXEC xp_logininfo @login, 'all'
    END TRY
    BEGIN CATCH
        SET @result = 2
    END CATCH
    
    IF ( SELECT COUNT(*)
         FROM   @login_info
       ) = 0
        OR NOT EXISTS ( SELECT  1
                        FROM    sys.server_principals
                        WHERE   name = @role ) 
        SET @result = 2
 
    IF ( SELECT COUNT(*)
         FROM   ( SELECT    p.permission_path,
                            r.name
                  FROM      @login_info p
                            INNER JOIN sys.server_principals pr 
                            ON p.permission_path = pr.name
                            INNER JOIN sys.server_role_members rm 
                            ON pr.principal_id = rm.member_principal_id
                            INNER JOIN sys.server_principals r 
                            ON rm.role_principal_id = r.principal_id
                  WHERE     p.account_name = @login
                            AND r.name = @role
                  UNION
                  SELECT    p.name,
                            r.name
                  FROM      sys.server_principals p
                            INNER JOIN sys.server_role_members rm 
                            ON p.principal_id = rm.member_principal_id
                            INNER JOIN sys.server_principals r 
                            ON rm.role_principal_id = r.principal_id
                  WHERE     p.name = @login
                            AND r.name = @role
                ) AS T
       ) > 0 
        SET @result = 1
        
    RETURN @result
GO

-- Test ----------------------------------------------
DECLARE @Res TINYINT
EXEC @Res = dbo.sp_logininfo 'sysadmin', 'AAA\bbb'
SELECT  @Res AS [Is AAA\bbb a mamber of sysadmin role?]

DECLARE @Res1 TINYINT
EXEC @Res1 = dbo.sp_logininfo 'sysadmin', 'sa'
SELECT  @Res1 AS [Is sa a mamber sysadmin role?]

There is a new article on TechNet Poland website. It was written by Marek Adamczuk and me. The article is about logon triggers - new functionality provided by SQL Server 2005 Service Pack 2.

Link to the article: http://www.microsoft.com/poland/technet/article/art0060_01.mspx.

Yesterday there was a party of Microsoft communities (this party was a part of Microsoft Security Summit & Developer Days 2007 event). The party was fine - lots of beer, lots of people, lots of fun. As always, I met some new people and spoke to some old friends. However, where were the angels from last year WSS community party? ;-)

This time no photos.

Today in the afternoon I will go to the Gromada Hotel to meet Steve Ballmer. Very exciting time.

This is a test post written with Windows Live Writer (Beta).

Download Windows Live Writer from http://windowslivewriter.spaces.live.com/.

In SQL Server 2005 you can easily obtain some useful information about index fragmentation using system views and sys.dm_db_index_physical_stats dynamic management function.

USE Northwind
GO
 
SELECT DISTINCT
  QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' 
  + QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Object], 
  i.name AS [Index], 
  i.type_desc [Index type],
  s.avg_fragmentation_in_percent AS [Fragmentation]
FROM sys.objects o
INNER JOIN sys.indexes AS i 
ON  i.[object_id] = o.[object_id] 
    AND i.name IS NOT NULL
INNER
 JOIN sys.dm_db_index_physical_stats(
    DB_ID(N'Northwind'),NULL,NULL,NULL,NULL
  ) AS s
ON
  i.index_id = s.index_id 
    AND i.[object_id] = s.[object_id]
ORDER
 BY [Fragmentation] DESC