Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHello John,
This will have to be done when your application authenticates. It will need
to create an audit of who logs in and when. This isn't a function of the
database but a function of the way your application is designed.
Aaron Weiker
http://aaronweiker.com/

> SQL 2K
> We developed a VB.NET application (SQL 2k db) where users can log into
> the system and extract reports. We are planning to create a report
> with user list who accessed the application in last 30 days.
> Can this be achieved by querying directly to the database '
> Thanks
> John|||Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database '
Thanks
JohnHi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database access log

SQL 2K
We developed a VB.NET application (SQL 2k db) where users can log into the
system and extract reports. We are planning to create a report with user
list who accessed the application in last 30 days.
Can this be achieved by querying directly to the database ?
Thanks
John
Hi John,
You can get this information, if you would have done one of the following;
(a) Enabled logging in your VB app (the user and datetime)
(b) Logging in the database tables. Again this should have been done with
your application. (the user and datetime)
(c) Enabled Auditing in the database server.
Are you using one super user to connect to SQL Server for all the
application users or are you connecting to SQL Server for every user
accessing your VB application?
In the former case, you will always see one user at the database level.
Thanks
Yogish

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B
Check out Application Roles
(http://msdn.microsoft.com/library/de...urity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:

>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>
|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:

> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>
|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas

"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:

>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:

> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B

Database Access Control

VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John BCheck out Application Roles
(http://msdn.microsoft.com/library/d...
rity_89ir.asp).
Basically, create one or more app roles in the database with the
different SQL permissions necessary granted to each one (eg. you may
have a standard user role and a special admin role). When a user
authenticates through your VB app, change the permissions of the SQL
client connection with sp_setapprole (the SPID gets a whole new set of
permissions associated with the app role that completely overrides the
DB user's permissions). The new set of permissions will remain in force
until the client connection drops out (ie. disconnects).
So you can just have a normal DB role, in which all DB users are
members, that has very limited permissions (something similar to
db_datareader + db_denydatawriter) and one or more application roles in
the DB that have much less restrictive permissions (like db_datareader +
db_datawriter, but obviously more granular that that) based on the
users' access levels stored in your access-level table.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
MS User wrote:

>VB.Net / SQL 2K
>We are developing a VB.Net application and the question is regarding the
>Login screen
>We have a table which stores the access-level for each users.
>Here is our requirement.
>1> Need to restrict users with readonly access when connected to the
>database NOT thru the application.
>2> Users will gain proper access after logging into the application.
>3> Once the user close the application, access-level back to 'read-only'
>The whole point is to restrict users not to directly modify data outside
>application.
>Thanks
>John B
>
>
>|||You achieve this through SQL Server's built-in security. Deny all
permissions on tables and grant users execute permission only on SPs.
All data access should be through parameterized SPs so that you can
apply your own rules and ensure the user only touches what they should.
David Portas
SQL Server MVP
--|||Suggest you have the application log on to the database with it's own logon,
which has read/write access. And give individual users their own logins
which have read-only access...
ALso, I strongly recommend that ALL access be allowed only through Stored
Procs, and direct access to tables be either prohibited, or restricted to
read-only, to all except for a narrrow group.
"MS User" wrote:

> VB.Net / SQL 2K
> We are developing a VB.Net application and the question is regarding the
> Login screen
> We have a table which stores the access-level for each users.
> Here is our requirement.
> 1> Need to restrict users with readonly access when connected to the
> database NOT thru the application.
> 2> Users will gain proper access after logging into the application.
> 3> Once the user close the application, access-level back to 'read-only'
> The whole point is to restrict users not to directly modify data outside
> application.
> Thanks
> John B
>
>
>|||Unless you are in the mood to do User Security, have your AD team create
Windows Global Groups, one each for each type of access. Grant these as
logins and map them to the database.
Create user-defined database roles and put security on these roles. Create
one each for each of the Windows Global Groups above.
Now, it is a one-to-one mapping from Windows Groups to SQL Server Database
Roles.
In your case, you could just have one database role, then make that role a
member of the system defined db_datareader and db_datadenywriter roles.
The above is for ad-hoc user access only.
For coded solutions, have your application use Windows Authentication and
grant that account as an SS login, mapped to the database.
Create another database role for the application. Make the role a member of
the system defined database roles db_datadenyreader and db_datadenywriter.
Then use stored procedures exclusively. Grant the user defined role execute
rights on all the stored procedures.
Sincerely,
Anthony Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:OxaNNRQJFHA.3340@.TK2MSFTNGP14.phx.gbl...
VB.Net / SQL 2K
We are developing a VB.Net application and the question is regarding the
Login screen
We have a table which stores the access-level for each users.
Here is our requirement.
1> Need to restrict users with readonly access when connected to the
database NOT thru the application.
2> Users will gain proper access after logging into the application.
3> Once the user close the application, access-level back to 'read-only'
The whole point is to restrict users not to directly modify data outside
application.
Thanks
John B