Express Database Security

Could somebody explain to me how does database security in the SQL Server Express environment works? It would seem that there has to be some level of "interplay" between the user's basic level of authority in Windows security on the machine (e.g., "Adminstrator" vs. "User"), the implications that that has on their level of access to the hard drive location where the database file is stored, and how security is configured within the database engine.

hi,

kschlap wrote:

Could somebody explain to me how does database security in the SQL Server Express environment works? It would seem that there has to be some level of "interplay" between the user's basic level of authority in Windows security on the machine (e.g., "Adminstrator" vs. "User"), the implications that that has on their level of access to the hard drive location where the database file is stored, and how security is configured within the database engine.

first of all, the engine runs under the Windows account it has been set to, like Network Service, LocalSystem (please do not), a local account or a domain account..

usually that account related NTFS permissions are validated for OS specific tasks like creating databases, performing backup and restore operations...

interactive users do usually not have to care about NTFS related permissions as they are managed at the account running the service level..

but SQL Server obviously has a security policy... before connecting interactive users, they are checked, both for integrated security and standard SQL Server authentication, agains the internal managed "list" of granted logins...

for integrated security, a round trip to the domain controller is performed, in order to get the interactive account's SID to be checked against that list, where, for standard SQL Server authenticated connections, user's name and password credentials are required to be checked against the registered standard SQL Server logins..

once that check is fulfilled, the interactive user is granted connection to the SQL Server instance..

by default, on pre Vista operating systems, local administrators are granted connection permissions as a group login is "generated" like BUILTIN\Administrators, granting them membership to the sys_admins server role.. so all local admins inherit full permissions on that instance...

another local group is granted connection, BUILTIN\Users, but no particular permissions are granted, nor is it made member of "powerfull" server's roles..

the second phase is related to database access... all logins but sysadmin members must be granted database access as well, which resolves creating database users related to server's logins..

with no presence of a related database user, a login is not granted permissions to access databases (if not member of sysadmins server role)..

additional granularity is then obtained providing permissions at object lever (for SELECT, etc as long as EXECUTE...) and even deeper at the column level for tables and views..

at the end of the game, as long as the interactive user has been granted permissions at SQL Server level, you do not have to care about NTFS permissions, as it's the account running SQL Server that will be checked.. so you have to provide NTFS permissions for that account..

a "minor" difference in this path regards attaching already detached databases, where SQL Server resets ACL on the detached files and you, as interactive user, have to be granted permissions at the NTFS level on that files before beeing able to reattach the database..

regards|||Thank you very much.
-Kyle