cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Alex_W
Level 6

How to check for DB Owner?

Hi

I need to run SQL Scripts during my install and I want to test if the user specified for the database connection is a DB_Owner to a SQL Server 2005 database. In the online help I found “Conditionally Launching a SQL Script in an InstallScript Installation”, but the “if(is(USER_ADMINISTRATOR, szUser)” example code always returns true for me when I use both Windows Authentication or SQL Authentication with a user with only public db access. Since I’m developing and testing on Vista, could this be a Vista bug? Is there another way to do test the users access rights to a SQL Server database?
Labels (1)
0 Kudos
(4) Replies
MichaelU
Level 12 Flexeran
Level 12 Flexeran

Hmm, there may be something I'm missing, but per the documentation on the Is() function, USER_ADMINISTRATOR only checks the current user:

USER_ADMINISTRATOR When the target operating system is Windows NT, does the current user have administrator privileges?
0 Kudos
Alex_W
Level 6

lol I guess this is an out-of-coffee error on my part. But is there anything built into IS that I can use with or inside of OnSQLServerInitialize() that would allow me to test database access rights of the user named in the connection string? If not I guess the only solution is connect to db and run some SQL that checks access rights of the user you just logged in as.
0 Kudos
Christopher_Pai
Level 16

That's pretty much what I do. Figure out exactly what your failure mode is and what priv you need to avoid it and then test for that condition.
0 Kudos
Marachkovski
Level 4

I execute next sql from InstallScript to check that user is db_owner.
szSQL = "USE [" + sqlDatabaseName + "];\n" +
" SELECT u.name\n" +
" FROM sysusers u, sysusers g, sysmembers m\n" +
" WHERE g.uid = m.groupuid\n" +
" AND g.issqlrole = 1\n" +
" AND u.uid = m.memberuid\n" +
" AND u.name = N'" + UserNameToVerify + "' AND g.name = N'db_owner'";
set pADORecordSetObj = pADOConnObj.Execute(szSQL);
if (pADORecordSetObj.BOF) then
->User is not db_owner
endif;
0 Kudos