Series: MSSQL Server reverse engineering (Part1)

[former_member185199]

Dear Reader,

in the last time i had to do a lot with reverse MSQL2008 databases and we found a couple of bugs, when reversing thru ODBC.

In this series i will share them all, but also how to find a workaround by yourself (especially if you need the solution NOW, and not in the next EBF)

Lets start with one of the lightweight bugs, i called him "wrong username" and he is reported to SAP under 1155554 / 2013, even when this one isn´t really related to reverse engineering, but we found it because of reversing users with domain which i never modeled before

Description: when having username with domain prefix ("domain\username") the user is generated well but in grantstatements the "\" is missing

To reproduce this bug:

create a PDM-Model  under Targetdb: MSSQL2008

create a table "Test"

create a user "domain\testuser"

grant select right for this user to table Test

Generate database selecting  the table and the user and permissions results in statements like this:

/*==============================================================*/

/* User: "HLB\a030926" */

/*==============================================================*/

create user "HLB\a030926" with default_schema = dbo

go

but the grantstatement goes wrong:

/*==============================================================*/

/* Table: testtable_permissions */

/*==============================================================*/

create table dbo.testtable_permissions (

testcol1 nchar(10) collate

SQL_Latin1_General_CP1_CI_AS null,

testcol2 nchar(10) collate

SQL_Latin1_General_CP1_CI_AS null,

testcol3 nchar(10) collate

SQL_Latin1_General_CP1_CI_AS null

)

on "PRIMARY"

go

grant INSERT,DELETE on dbo.testtable_permissions to "HLBa030926"

go

here is the fix:

1. edit current dbms

2. search for Microsoft SQL Server 2008::Script\Objects\Permission\Create

3 insert the following code:

grant %PERMLIST% on [%QUALIFIER%]%OBJECT% to \[

.vbscript( %GRANTEE%)

dim grantee

     grantee= ScriptInputArray(0)

     for each u in activemodel.users

          dim p

          for each p in u.permissions

               if Replace(p.DBIdentifier.name,chr(92),"") = grantee then

                    ScriptResult = p.DBIdentifier.name

                    grantee = p.DBIdentifier.name

          end if

     next

next

.endvbscript

\] [%GRANTOPTION%]

What are we doing here ?

the script gets the username (grantee) with the striped backslash, we take the list of all users that have permissions and try to compare their names (by stripping existing "\" ) with our input. if we have a match then this user is our output

so long, hope to write you soon

dj