Series: MSSQL Server reverse engineering (Part1)
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