SqlExpress distribution and access rights

[Peter Hase]

Hi, I'm using sqlexpress 2005 with a .NET
applikation. Security is set to integrated mode.
When deployed, the app's database will be created via
script by the installer.
Now, by default this database is not accessable if
the current user is not logged on as (xp-)administrator.

As workaround it is possible to add the server
role "sysadmin" to the account "predefined\user" (with
sp_addsrvrolemember).

This does not look adequate for me. One problem
is (additional to possible security issues) that
the account string seems to be culture/language
dependent and may not work everywhere.
Is there any other way to provide access rights
for this situation?

Thanks for help
Peter

[Norman Yuan]

Is this app with SQL Server Epxress a server app or a desktop app?

Since you want user(s) access the SQL Server with Windows integrated
security, and you, of course, do not know what user account the user is
using when developing the app. The solution could be this:

Create a SQL Server login that maps to a local Windows user group, not a
user account, say named as "TheAppSqlUsers". Then you add that SQL Server
login to the targeting database as a proper user/role, assign all the
permissions required by the app accordingly. So, when install the app, the
computer where the SQL Server sits need to have a local user group called
"TheAppSqlUsers" created.

With this configuration, when user uses your app, as long as his Windows
login account (domain or local) is in that user group ("TheAppSqlUsers"), he
all have all required permission. Similarly, if your app wnats different
permissions to access different data in the SQL server database, then you
can have different SQL Server logins, mapping to different local user
groups.

All this can be done manually before or after your app gets installed. Of
course, you can also incorporate the Windows local user group setup into
your installation package (I am afaid that you have write a seperate app to
manipulate window/local or network domain user account and then to have
installer to run it during installation).

Hope this gives you some idea.

Post by Peter Hase
Hi, I'm using sqlexpress 2005 with a .NET
applikation. Security is set to integrated mode.
When deployed, the app's database will be created via
script by the installer.
Now, by default this database is not accessable if
the current user is not logged on as (xp-)administrator.
As workaround it is possible to add the server
role "sysadmin" to the account "predefined\user" (with
sp_addsrvrolemember).
This does not look adequate for me. One problem
is (additional to possible security issues) that
the account string seems to be culture/language
dependent and may not work everywhere.
Is there any other way to provide access rights
for this situation?
Thanks for help
Peter

[Peter Hase]

Post by Norman Yuan
Is this app with SQL Server Epxress a server app or a desktop app?

A desktop app (this may change later)

Post by Norman Yuan
Create a SQL Server login that maps to a local Windows user group, not a
user account, say named as "TheAppSqlUsers". Then you add that SQL Server
login to the targeting database as a proper user/role, assign all the
permissions required by the app accordingly. So, when install the app, the
computer where the SQL Server sits need to have a local user group called
"TheAppSqlUsers" created.

That's the problem. The host machine may be very remote.

Post by Norman Yuan
... also incorporate the Windows local user group setup into
your installation package

That's what I did. A very helful .NET component here was SMO.

Post by Norman Yuan
Hope this gives you some idea.

Yes thanks!

Best regards
Peter