Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> No. What if some user inserts/deletes the row Xwhile Xyou are reading.You
> are about to get an inconsistent data. For example
> Xyou have tree pages with data Xlike a) 10,40,60 b) 80,100,90 Xc)110,70,85 ,
> so while Xyou read page A Xanother connection inserst the value let mesay
> 50, but you have already read data Xfrom the page A , so it moves the all
> data to a new created page so now that data looks like Xa)10,40 ,50,
> b) 80,100,90 Xc)110,70,85 ,d)60 ... and Xas you keep reading Xyou get
> 60(duplicate) from page D as well.
>
> Yes , you can use TABLOCK hint Xor if you use SQL Server 2005 Xtake a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
>
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
>
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Friday, March 30, 2012
Reading .LDF
Dear all,
How to identity all those changes done in a db for any user in a specific
period of time?
I am not talking about put triggers in each object or something like that
(on the other hand such actions never will reach exhaustely all the actions
commited)
For example:
developer1 modify a view at 16:00 and then developer2 delete it.
Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
level (hexadecimal info)
I've been able to to order theses scenarios (values as follows belong to
field 'OPERATION':
0)AFTER TOTAL SHRINK (O KB)
LOP_BEGIN_CKPT
LOP_END_CKPT
LOP_FILE_HDR_MODIFY
1)FOR INSERT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_COMMIT_XACT
2)FOR UPDATE STATEMENT:
LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT
3)FOR CREATE OBJECT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_MARK_DDL
It seems very tricky but I suppose that there would be some method for
obtain such information. How can I do this?
Perhaps, it's a useless madness, obsessive.
--
Current location: Alicante (ES)Enric
Visit at
http://www.lumigent.com/ --explorer log for sql
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)|||> How to identity all those changes done in a db for any user in a specific
> period of time?
In hindsight:
http://www.aspfaq.com/2449
Moving forward:
http://www.aspfaq.com/2496|||Thanks a lot to both
--
Current location: Alicante (ES)
"Aaron Bertrand [SQL Server MVP]" wrote:
> In hindsight:
> http://www.aspfaq.com/2449
> Moving forward:
> http://www.aspfaq.com/2496
>
>|||There are 3rd party tools for mining information from the transaction log.
However, the intended purpose of this log is for maintaining transactions,
not auditing, so you may find it limiting if your intent is monitoring user
activity.
With a little studying and configuration, you can trace most any desired
event using SQL Server Profiler, which is a tool installed with SQL Server.
Depending on what event categories you choose, you can trace not just data
or object modifications but also things like SP executions and even table
selects. It is basically a service that runs on server, and it's output can
be archived to a file or table. You will need admin rights to use this tool.
http://msdn.microsoft.com/library/d...>
erf_86ib.asp
[url]http://www.sqlservercentral.com/columnists/bkelley/auditingwithsqlprofiler.asp[/ur
l]
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)
How to identity all those changes done in a db for any user in a specific
period of time?
I am not talking about put triggers in each object or something like that
(on the other hand such actions never will reach exhaustely all the actions
commited)
For example:
developer1 modify a view at 16:00 and then developer2 delete it.
Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
level (hexadecimal info)
I've been able to to order theses scenarios (values as follows belong to
field 'OPERATION':
0)AFTER TOTAL SHRINK (O KB)
LOP_BEGIN_CKPT
LOP_END_CKPT
LOP_FILE_HDR_MODIFY
1)FOR INSERT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_COMMIT_XACT
2)FOR UPDATE STATEMENT:
LOP_BEGIN_XACT
LOP_MODIFY_ROW
LOP_COMMIT_XACT
3)FOR CREATE OBJECT STATEMENT:
LOP_BEGIN_XACT
LOP_INSERT_ROWS
LOP_MARK_DDL
It seems very tricky but I suppose that there would be some method for
obtain such information. How can I do this?
Perhaps, it's a useless madness, obsessive.
--
Current location: Alicante (ES)Enric
Visit at
http://www.lumigent.com/ --explorer log for sql
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)|||> How to identity all those changes done in a db for any user in a specific
> period of time?
In hindsight:
http://www.aspfaq.com/2449
Moving forward:
http://www.aspfaq.com/2496|||Thanks a lot to both
--
Current location: Alicante (ES)
"Aaron Bertrand [SQL Server MVP]" wrote:
> In hindsight:
> http://www.aspfaq.com/2449
> Moving forward:
> http://www.aspfaq.com/2496
>
>|||There are 3rd party tools for mining information from the transaction log.
However, the intended purpose of this log is for maintaining transactions,
not auditing, so you may find it limiting if your intent is monitoring user
activity.
With a little studying and configuration, you can trace most any desired
event using SQL Server Profiler, which is a tool installed with SQL Server.
Depending on what event categories you choose, you can trace not just data
or object modifications but also things like SP executions and even table
selects. It is basically a service that runs on server, and it's output can
be archived to a file or table. You will need admin rights to use this tool.
http://msdn.microsoft.com/library/d...>
erf_86ib.asp
[url]http://www.sqlservercentral.com/columnists/bkelley/auditingwithsqlprofiler.asp[/ur
l]
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:A38C627F-9C4C-44B6-B012-F94BA91DBFB5@.microsoft.com...
> Dear all,
> How to identity all those changes done in a db for any user in a specific
> period of time?
> I am not talking about put triggers in each object or something like that
> (on the other hand such actions never will reach exhaustely all the
> actions
> commited)
> For example:
> developer1 modify a view at 16:00 and then developer2 delete it.
> Using DBCC LOG(LDF,2) oneself can see actions committed but at very low
> level (hexadecimal info)
> I've been able to to order theses scenarios (values as follows belong to
> field 'OPERATION':
> 0)AFTER TOTAL SHRINK (O KB)
> LOP_BEGIN_CKPT
> LOP_END_CKPT
> LOP_FILE_HDR_MODIFY
> 1)FOR INSERT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_COMMIT_XACT
> 2)FOR UPDATE STATEMENT:
> LOP_BEGIN_XACT
> LOP_MODIFY_ROW
> LOP_COMMIT_XACT
>
> 3)FOR CREATE OBJECT STATEMENT:
> LOP_BEGIN_XACT
> LOP_INSERT_ROWS
> LOP_MARK_DDL
>
> It seems very tricky but I suppose that there would be some method for
> obtain such information. How can I do this?
> Perhaps, it's a useless madness, obsessive.
> --
> Current location: Alicante (ES)
Wednesday, March 28, 2012
Read/Only access to Northwind database
Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htmTry:
1) Create a login
2) Add the login as a user to the Northwind database
3) Add the user to the db_datareader fixed database role
Be sure that you remove the guest user, if present.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htm|||Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:
> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to Northwind
.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" - [url]http://www.windwardreports.com/film.htm[/ur
l]
>
>|||Hi Dave,
I think you've done it right and haven't miseed anything. The public role
is a special database role to which every database
user belongs. The public role contain default access permissionsfor any
user who can access the database. This database role cannot
be dropped.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial
response from the community or a Microsoft Support Engineer within 1
business day is
acceptable. Please note that each follow up response may take approximately
2 business days
as the support professional working with you may need further investigation
to reach the
most efficient resolution. The offering is not appropriate for situations
that require
urgent, real-time or phone-based interactions or complex project analysis
and dump analysis
issues. Issues of this nature are best handled working with a dedicated
Microsoft Support
Engineer by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||A "fixed database role" is one that is there by default and cannot be
removed. There are also fixed server roles. You can create your own
database roles, too. However, you cannot create server roles.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:C32D630F-14E6-4A85-96CF-171DCD4D1A48@.microsoft.com...
Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:
> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to
> Northwind.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" -
> http://www.windwardreports.com/film.htm
>
>
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htmTry:
1) Create a login
2) Add the login as a user to the Northwind database
3) Add the user to the db_datareader fixed database role
Be sure that you remove the guest user, if present.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htm|||Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:
> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to Northwind
.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" - [url]http://www.windwardreports.com/film.htm[/ur
l]
>
>|||Hi Dave,
I think you've done it right and haven't miseed anything. The public role
is a special database role to which every database
user belongs. The public role contain default access permissionsfor any
user who can access the database. This database role cannot
be dropped.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial
response from the community or a Microsoft Support Engineer within 1
business day is
acceptable. Please note that each follow up response may take approximately
2 business days
as the support professional working with you may need further investigation
to reach the
most efficient resolution. The offering is not appropriate for situations
that require
urgent, real-time or phone-based interactions or complex project analysis
and dump analysis
issues. Issues of this nature are best handled working with a dedicated
Microsoft Support
Engineer by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||A "fixed database role" is one that is there by default and cannot be
removed. There are also fixed server roles. You can create your own
database roles, too. However, you cannot create server roles.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:C32D630F-14E6-4A85-96CF-171DCD4D1A48@.microsoft.com...
Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:
> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to
> Northwind.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" -
> http://www.windwardreports.com/film.htm
>
>
Read the transaction log (ldf) file. My first post ever :)
Hi!
The purpose is to se history of which user updated, inserted or deleted a row in the database.
Can't seem to find any publications from microsoft on how to parse and interpret the log file.
Any documentation on how the log file is structured?
Is it possible to obtain this information through the system views
I don't want to use triggers.
Thanks
Consider the use of a third-party application for this job. That is money that's well spent.
|||Thanks for the reply.
I know there exist som applications that can read log files, like Lumigent Log Explorer.
But it would be nice with an freeware version
If you really want to learn, here the docuemnt on basic level.. I found few months back very unexpectedly... Nice one....
But this document is not sufficient to start programing with Tx Log files.. As Frank says you can use the 3rd party tools.. there are lot of products available on the market...
https://www.blackhat.com/presentations/bh-usa-07/Fowler/Presentation/bh-usa-07-fowler.pdf
|||
J-A.G. wrote:
I know there exist som applications that can read log files, like Lumigent Log Explorer.
But it would be nice with an freeware version
TANSTAAFL
But in order to learn you can always use something like the undocumented
DBCC LOG (Northwind, -1)
|||good frank!
This undocumented command works on sql2000?
|||Yes it does. But as with all undocumented features the use is at your own risk.
|||Tks!Monday, March 26, 2012
Read only User on data base
I'm sure this is a really easy one but I want to create a user with read only access on a sql 2000 database using SQL authentication.
I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.
I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.
thanks,Grant the user as db_datareader. He or she will can only read data and uses EM and QA.|||I've Granted the user the db_datareader permission and within EM everything is ok the user has read only access but within QA, logging in with the correct SQL Ath. user name and password he still has permission to Drop tables via the object browser -- very strange or have I done something wrong !|||Is the id in a particular role?
How many roles do you have?|||The User has been created within the Public and DB_datareader database role only.
Do I need to add him to another db role or server role?|||Nope. Nothing wrong. You just have to change the password on the account he is using in Query Analyzer. Basically, this user has access to two separate accounts. One with the permissions you want him to have (NT Authenticated), and a second (the SQL authenticated one) with permissions you do not want him to have.
Naturally, before you go changing passwords, you have to ask where the other (SQL authenticated) account is used (maybe in some application that needs to update data?). In this case, changing the password could be prohibitive, and you may have to resort to asking the user to stop using that account. Ahh, politics.
I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.
I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.
thanks,Grant the user as db_datareader. He or she will can only read data and uses EM and QA.|||I've Granted the user the db_datareader permission and within EM everything is ok the user has read only access but within QA, logging in with the correct SQL Ath. user name and password he still has permission to Drop tables via the object browser -- very strange or have I done something wrong !|||Is the id in a particular role?
How many roles do you have?|||The User has been created within the Public and DB_datareader database role only.
Do I need to add him to another db role or server role?|||Nope. Nothing wrong. You just have to change the password on the account he is using in Query Analyzer. Basically, this user has access to two separate accounts. One with the permissions you want him to have (NT Authenticated), and a second (the SQL authenticated one) with permissions you do not want him to have.
Naturally, before you go changing passwords, you have to ask where the other (SQL authenticated) account is used (maybe in some application that needs to update data?). In this case, changing the password could be prohibitive, and you may have to resort to asking the user to stop using that account. Ahh, politics.
Read only user and not seeing Tables, views etc
I would like to be able to setup a user that can only select information
from a View.
But when I enable security for this user, the user can see all the
tables, Views, Store Proc etc. I know it is only the names, but I don't
want the user to be able to do this, as he is only allowed to view the
view I have enabled for him.
The user can log in using Enterprise Manager and flick through the
tables, views etc, but can't read them, but he can get the names which
is what I don't want to happen.
Is there anyway of stopping the user from seeing all the other
information?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
In Enterprise manager you cant control that.
Thanks
Hari
MCDBA
"Bryan Avery" <b_avery@.yahoo.com> wrote in message
news:ObOG040BEHA.892@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to setup a user that can only select information
> from a View.
> But when I enable security for this user, the user can see all the
> tables, Views, Store Proc etc. I know it is only the names, but I don't
> want the user to be able to do this, as he is only allowed to view the
> view I have enabled for him.
> The user can log in using Enterprise Manager and flick through the
> tables, views etc, but can't read them, but he can get the names which
> is what I don't want to happen.
> Is there anyway of stopping the user from seeing all the other
> information?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
from a View.
But when I enable security for this user, the user can see all the
tables, Views, Store Proc etc. I know it is only the names, but I don't
want the user to be able to do this, as he is only allowed to view the
view I have enabled for him.
The user can log in using Enterprise Manager and flick through the
tables, views etc, but can't read them, but he can get the names which
is what I don't want to happen.
Is there anyway of stopping the user from seeing all the other
information?
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
In Enterprise manager you cant control that.
Thanks
Hari
MCDBA
"Bryan Avery" <b_avery@.yahoo.com> wrote in message
news:ObOG040BEHA.892@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to setup a user that can only select information
> from a View.
> But when I enable security for this user, the user can see all the
> tables, Views, Store Proc etc. I know it is only the names, but I don't
> want the user to be able to do this, as he is only allowed to view the
> view I have enabled for him.
> The user can log in using Enterprise Manager and flick through the
> tables, views etc, but can't read them, but he can get the names which
> is what I don't want to happen.
> Is there anyway of stopping the user from seeing all the other
> information?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Read Only User - One Database
How do I create a user, that has rights to only one database, and only
select rights to some views?
I created a login in Enterprise Manager; checked the database access only on
the database the user needs to see; and checked the db_datareader role.
However, logging in as this user I can see and select from other databases.
Under the database I gave the user rights to, I checked only select rights
on the views I want the user to select from. I can still select from
tables, etc.
Obviously, I'm missing something here...Which other databases? Most likely those are databases with
the guest account enabled. If a user doesn't have an account
to log into the database and the guest account is enabled,
the user has access through the guest account and whatever
rights are granted to public and this account. The guest
account cannot be deleted from master or tempdb. It can be
added, delete from other databases.
When you added the user to the db_datareader role, you gave
that user permissions to select from all user tables.
Permissions are cumulative so the user obtains all
permissions through the combination of their individual
account and any groups, roles that they are members of. When
you also gave the individual account select permission on
views, the user ended up with those permissions as well as
select on all user tables.
-Sue
On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:
>How do I create a user, that has rights to only one database, and only
>select rights to some views?
>I created a login in Enterprise Manager; checked the database access only o
n
>the database the user needs to see; and checked the db_datareader role.
>However, logging in as this user I can see and select from other databases.
>Under the database I gave the user rights to, I checked only select rights
>on the views I want the user to select from. I can still select from
>tables, etc.
>Obviously, I'm missing something here...
>|||sue i can't see guest account except master database.. you meant somebody
created it ?
"Sue Hoegemeier" wrote:
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue
> On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
>|||No...nothing about someone creating the guest account but I
don't know what databases the user is accessing. Which other
databases? System databases or user databases? Is the user a
member of other roles or groups that would give them access
to the other databases?
-Sue
On Thu, 28 Oct 2004 19:53:01 -0700, "Dave"
<Dave@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>sue i can't see guest account except master database.. you meant somebody
>created it ?
>"Sue Hoegemeier" wrote:
>|||I've deleted the guest account from other user DBs and the user I can not
see/connect to those DBs - good.
I've unchecked the db_datareader role and only gave select permissions to 2
views in the DB - that seems to work OK as well.
However, the user can still select from system tables in the database they
are assigned to. Any way to prevent that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue|||The public role is able to select from some of the system tables. All
database user are members of public. That's why the user can select
from some of the system tables.
-Sue
On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:
>I've deleted the guest account from other user DBs and the user I can not
>see/connect to those DBs - good.
>I've unchecked the db_datareader role and only gave select permissions to 2
>views in the DB - that seems to work OK as well.
>However, the user can still select from system tables in the database they
>are assigned to. Any way to prevent that?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>|||Adrian
In additon to Sue's advice ,if you don't want the users be able to select
from system tables , you can hide them by editing SQL Server registration
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
> The public role is able to select from some of the system tables. All
> database user are members of public. That's why the user can select
> from some of the system tables.
> -Sue
> On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
2[vbcol=seagreen]
they[vbcol=seagreen]
>|||That doesn't prevent users from being able to select from
system tables though. All that will do is prevent displaying
them in Enterprise Manager.
-Sue
On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
<urid@.iscar.co.il> wrote:
>Adrian
>In additon to Sue's advice ,if you don't want the users be able to select
>from system tables , you can hide them by editing SQL Server registration
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
>2
>they
>|||Yep, I assumed that all manipulations are done by users in EM.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qi5bo0lo2223mjjdqcfkdlhvafhlg596i0@.
4ax.com...
> That doesn't prevent users from being able to select from
> system tables though. All that will do is prevent displaying
> them in Enterprise Manager.
> -Sue
> On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>
not[vbcol=seagreen]
to[vbcol=seagreen]
>|||I am trying to do the same thing. I have a user that needs access to one of
the databases on the server. I can prevent him from modifying or even seeing
the tables on the other databases with permissions, but I want to be able to
hide the other databases from Enterprise Manager. I am using SQL logins. Can
I do this? It sounds like you were able to do this. I have deleted the guest
account from a number of databases and added his login to only two databases
but the login displays all of the databases, including those with only sa as
the login.
"Adrian Maull (MCP)" wrote:
> I've deleted the guest account from other user DBs and the user I can not
> see/connect to those DBs - good.
> I've unchecked the db_datareader role and only gave select permissions to
2
> views in the DB - that seems to work OK as well.
> However, the user can still select from system tables in the database they
> are assigned to. Any way to prevent that?
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>
>
select rights to some views?
I created a login in Enterprise Manager; checked the database access only on
the database the user needs to see; and checked the db_datareader role.
However, logging in as this user I can see and select from other databases.
Under the database I gave the user rights to, I checked only select rights
on the views I want the user to select from. I can still select from
tables, etc.
Obviously, I'm missing something here...Which other databases? Most likely those are databases with
the guest account enabled. If a user doesn't have an account
to log into the database and the guest account is enabled,
the user has access through the guest account and whatever
rights are granted to public and this account. The guest
account cannot be deleted from master or tempdb. It can be
added, delete from other databases.
When you added the user to the db_datareader role, you gave
that user permissions to select from all user tables.
Permissions are cumulative so the user obtains all
permissions through the combination of their individual
account and any groups, roles that they are members of. When
you also gave the individual account select permission on
views, the user ended up with those permissions as well as
select on all user tables.
-Sue
On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:
>How do I create a user, that has rights to only one database, and only
>select rights to some views?
>I created a login in Enterprise Manager; checked the database access only o
n
>the database the user needs to see; and checked the db_datareader role.
>However, logging in as this user I can see and select from other databases.
>Under the database I gave the user rights to, I checked only select rights
>on the views I want the user to select from. I can still select from
>tables, etc.
>Obviously, I'm missing something here...
>|||sue i can't see guest account except master database.. you meant somebody
created it ?
"Sue Hoegemeier" wrote:
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue
> On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
>|||No...nothing about someone creating the guest account but I
don't know what databases the user is accessing. Which other
databases? System databases or user databases? Is the user a
member of other roles or groups that would give them access
to the other databases?
-Sue
On Thu, 28 Oct 2004 19:53:01 -0700, "Dave"
<Dave@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>sue i can't see guest account except master database.. you meant somebody
>created it ?
>"Sue Hoegemeier" wrote:
>|||I've deleted the guest account from other user DBs and the user I can not
see/connect to those DBs - good.
I've unchecked the db_datareader role and only gave select permissions to 2
views in the DB - that seems to work OK as well.
However, the user can still select from system tables in the database they
are assigned to. Any way to prevent that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue|||The public role is able to select from some of the system tables. All
database user are members of public. That's why the user can select
from some of the system tables.
-Sue
On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:
>I've deleted the guest account from other user DBs and the user I can not
>see/connect to those DBs - good.
>I've unchecked the db_datareader role and only gave select permissions to 2
>views in the DB - that seems to work OK as well.
>However, the user can still select from system tables in the database they
>are assigned to. Any way to prevent that?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>|||Adrian
In additon to Sue's advice ,if you don't want the users be able to select
from system tables , you can hide them by editing SQL Server registration
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
> The public role is able to select from some of the system tables. All
> database user are members of public. That's why the user can select
> from some of the system tables.
> -Sue
> On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
2[vbcol=seagreen]
they[vbcol=seagreen]
>|||That doesn't prevent users from being able to select from
system tables though. All that will do is prevent displaying
them in Enterprise Manager.
-Sue
On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
<urid@.iscar.co.il> wrote:
>Adrian
>In additon to Sue's advice ,if you don't want the users be able to select
>from system tables , you can hide them by editing SQL Server registration
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
>2
>they
>|||Yep, I assumed that all manipulations are done by users in EM.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qi5bo0lo2223mjjdqcfkdlhvafhlg596i0@.
4ax.com...
> That doesn't prevent users from being able to select from
> system tables though. All that will do is prevent displaying
> them in Enterprise Manager.
> -Sue
> On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>
not[vbcol=seagreen]
to[vbcol=seagreen]
>|||I am trying to do the same thing. I have a user that needs access to one of
the databases on the server. I can prevent him from modifying or even seeing
the tables on the other databases with permissions, but I want to be able to
hide the other databases from Enterprise Manager. I am using SQL logins. Can
I do this? It sounds like you were able to do this. I have deleted the guest
account from a number of databases and added his login to only two databases
but the login displays all of the databases, including those with only sa as
the login.
"Adrian Maull (MCP)" wrote:
> I've deleted the guest account from other user DBs and the user I can not
> see/connect to those DBs - good.
> I've unchecked the db_datareader role and only gave select permissions to
2
> views in the DB - that seems to work OK as well.
> However, the user can still select from system tables in the database they
> are assigned to. Any way to prevent that?
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>
>
Read Only permission for ReportingServices.GetPolicies()
I would like for a user to view the groups that have permssion for a
particular folder in SQL Reporting Services that they have access to via the
Web Services by calling ReportingServices.GetPolicies(). I realize
(according to MS's website and trial and error) that the user must have the
"Read Security Policies" right for that folder. The only way I have found
to set this via a Role is to allow "Set security for individual items".
Is there any way to allow a user to only view the security information via
ReportingServices.GetPolicies() without allowing them to change the security
policies?
Any help would be greatly appreciated. Thank you.
Jeremy M. WhiteWell, I found a way to do it, but I'm not really excited about it.
Currently, I am selecting directly from the SQL Reporting services database.
Again, I would prefer to do it through the web services, but I guess this
will suffice. For anyone interested, here is the SQL statement to retrieve
the policy information:
SELECT
U.UserName, U.UserType, R.RoleName, R.Description
FROM
Catalog As C INNER JOIN
PolicyUserRole As PU
ON (C.PolicyID = PU.PolicyID)
INNER JOIN
Roles As R
ON (PU.RoleID = R.RoleID)
INNER JOIN
Users As U
ON (PU.UserID = U.UserID)
WHERE
C.Path = @.ItemName
If anyone is able to determine how to provide read-only access to the
policies via a role, please let me know.
Thanks,
Jeremy
"Jeremy M. White" <jeremy_white@.dart.biz> wrote in message
news:uEkE3mbzEHA.1300@.TK2MSFTNGP14.phx.gbl...
> I would like for a user to view the groups that have permssion for a
> particular folder in SQL Reporting Services that they have access to via
the
> Web Services by calling ReportingServices.GetPolicies(). I realize
> (according to MS's website and trial and error) that the user must have
the
> "Read Security Policies" right for that folder. The only way I have found
> to set this via a Role is to allow "Set security for individual items".
> Is there any way to allow a user to only view the security information via
> ReportingServices.GetPolicies() without allowing them to change the
security
> policies?
> Any help would be greatly appreciated. Thank you.
> Jeremy M. White
>
particular folder in SQL Reporting Services that they have access to via the
Web Services by calling ReportingServices.GetPolicies(). I realize
(according to MS's website and trial and error) that the user must have the
"Read Security Policies" right for that folder. The only way I have found
to set this via a Role is to allow "Set security for individual items".
Is there any way to allow a user to only view the security information via
ReportingServices.GetPolicies() without allowing them to change the security
policies?
Any help would be greatly appreciated. Thank you.
Jeremy M. WhiteWell, I found a way to do it, but I'm not really excited about it.
Currently, I am selecting directly from the SQL Reporting services database.
Again, I would prefer to do it through the web services, but I guess this
will suffice. For anyone interested, here is the SQL statement to retrieve
the policy information:
SELECT
U.UserName, U.UserType, R.RoleName, R.Description
FROM
Catalog As C INNER JOIN
PolicyUserRole As PU
ON (C.PolicyID = PU.PolicyID)
INNER JOIN
Roles As R
ON (PU.RoleID = R.RoleID)
INNER JOIN
Users As U
ON (PU.UserID = U.UserID)
WHERE
C.Path = @.ItemName
If anyone is able to determine how to provide read-only access to the
policies via a role, please let me know.
Thanks,
Jeremy
"Jeremy M. White" <jeremy_white@.dart.biz> wrote in message
news:uEkE3mbzEHA.1300@.TK2MSFTNGP14.phx.gbl...
> I would like for a user to view the groups that have permssion for a
> particular folder in SQL Reporting Services that they have access to via
the
> Web Services by calling ReportingServices.GetPolicies(). I realize
> (according to MS's website and trial and error) that the user must have
the
> "Read Security Policies" right for that folder. The only way I have found
> to set this via a Role is to allow "Set security for individual items".
> Is there any way to allow a user to only view the security information via
> ReportingServices.GetPolicies() without allowing them to change the
security
> policies?
> Any help would be greatly appreciated. Thank you.
> Jeremy M. White
>
Friday, March 23, 2012
read logs
how to know which user was executed the SQL statemant and operations.
Vaidas Gudas wrote:
> how to know which user was executed the SQL statemant and operations.
You can implement your own auditing through triggers, you could use SQL
Profiler or use third-party tools to read the logs:
http://www.lumigent.com/products/le_sql.html
David Portas
SQL Server MVP
sql
Vaidas Gudas wrote:
> how to know which user was executed the SQL statemant and operations.
You can implement your own auditing through triggers, you could use SQL
Profiler or use third-party tools to read the logs:
http://www.lumigent.com/products/le_sql.html
David Portas
SQL Server MVP
sql
read logs
how to know which user was executed the SQL statemant and operations.Vaidas Gudas wrote:
> how to know which user was executed the SQL statemant and operations.
You can implement your own auditing through triggers, you could use SQL
Profiler or use third-party tools to read the logs:
http://www.lumigent.com/products/le_sql.html
David Portas
SQL Server MVP
--
> how to know which user was executed the SQL statemant and operations.
You can implement your own auditing through triggers, you could use SQL
Profiler or use third-party tools to read the logs:
http://www.lumigent.com/products/le_sql.html
David Portas
SQL Server MVP
--
read logs
how to know which user was executed the SQL statemant and operations.Vaidas Gudas wrote:
> how to know which user was executed the SQL statemant and operations.
You can implement your own auditing through triggers, you could use SQL
Profiler or use third-party tools to read the logs:
http://www.lumigent.com/products/le_sql.html
--
David Portas
SQL Server MVP
--
> how to know which user was executed the SQL statemant and operations.
You can implement your own auditing through triggers, you could use SQL
Profiler or use third-party tools to read the logs:
http://www.lumigent.com/products/le_sql.html
--
David Portas
SQL Server MVP
--
Tuesday, March 20, 2012
Re: How to encrypt a column in SQL Server
Hi everyone,
How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.
Thanks in advance.To make the control show asterisks, set its password attribute.
To encrypt the data, pick an encryption algorithm and implement that algorithm in your Access MDB or data project.
-PatP|||we did this one for her already
http://www.dbforums.com/t1095162.html|||Yeah, I know we've done it before... I think more than once actually.
I just figure that if we consistantly give the same answers, eventually it will help solve the problem. Maybe I should have just posted a link to one of the previous answers, but I was feeling too lazy to go find it just then. I've been kind of busy lately, and that often leads me to take the easy answer instead of doing it up properly, especially if the easy and proper ways both generate the same results!
-PatP|||but it is kind of a waste of my employers hard paid salary to have me redudently goofing off.|||I suppose that's true, but they are rarely discriminating about goofing off. I've never heard of a case where redundant goofing off was treated differently than applied goofing off was.
-PatP|||Owhay ancay ooyay encryptay anay olumnkay innay SQLAY Erversay? Unway ethodmay issay ootay oosyay uhthay amousfay "Igpay Atinlay" gorithmlay. Rytay ittay eforebay ooyay ecideday ootay ostpay uthay amesay amnday estionquay againay.
Okay?
How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.
Thanks in advance.To make the control show asterisks, set its password attribute.
To encrypt the data, pick an encryption algorithm and implement that algorithm in your Access MDB or data project.
-PatP|||we did this one for her already
http://www.dbforums.com/t1095162.html|||Yeah, I know we've done it before... I think more than once actually.
I just figure that if we consistantly give the same answers, eventually it will help solve the problem. Maybe I should have just posted a link to one of the previous answers, but I was feeling too lazy to go find it just then. I've been kind of busy lately, and that often leads me to take the easy answer instead of doing it up properly, especially if the easy and proper ways both generate the same results!
-PatP|||but it is kind of a waste of my employers hard paid salary to have me redudently goofing off.|||I suppose that's true, but they are rarely discriminating about goofing off. I've never heard of a case where redundant goofing off was treated differently than applied goofing off was.
-PatP|||Owhay ancay ooyay encryptay anay olumnkay innay SQLAY Erversay? Unway ethodmay issay ootay oosyay uhthay amousfay "Igpay Atinlay" gorithmlay. Rytay ittay eforebay ooyay ecideday ootay ostpay uthay amesay amnday estionquay againay.
Okay?
Re: How can you encrypt a column in SQL Server?
Hi everyone,
How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.
Thanks in advance.Pick an encryption method, and handle it on the client. ROT13 is fine for most casual stuff, BlowFish is good for the really hairy stuff.
-PatP|||In access, right click on your textbox and go to properties. on the data tab, put Password in the input mask field.
this how ever is not encryption. There are third party dll available that use the RSA cypher and other methods to encrpyt data with public key encryption and you can write the encrypted data to SQL server but you can not really encrypt a column per se.|||You may want to look into SQLShield, but as it was noted before, - the encryption should be done in the front-end code, so that clear-text passwords are not travelling the network.|||sql server 2k gives you the ability to set permissions on a single column in a table if you wish.
however, you could give users access to the table through a view and not include the pwd column in the view definition.
just an alternative.
How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.
Thanks in advance.Pick an encryption method, and handle it on the client. ROT13 is fine for most casual stuff, BlowFish is good for the really hairy stuff.
-PatP|||In access, right click on your textbox and go to properties. on the data tab, put Password in the input mask field.
this how ever is not encryption. There are third party dll available that use the RSA cypher and other methods to encrpyt data with public key encryption and you can write the encrypted data to SQL server but you can not really encrypt a column per se.|||You may want to look into SQLShield, but as it was noted before, - the encryption should be done in the front-end code, so that clear-text passwords are not travelling the network.|||sql server 2k gives you the ability to set permissions on a single column in a table if you wish.
however, you could give users access to the table through a view and not include the pwd column in the view definition.
just an alternative.
Friday, March 9, 2012
RDL to RDLC Dataset Bind Question
We got some existing Reports that are set to RDL files but now they want to be able to create a web user interface so the user can interact through such method. As I am finding out, these reports need to be converted to RDLC so that they can all the proper information so they can be binded. My question is, the datasets are already set (I beleive it's the .rdlc.data files) on the RDL solution project. Now I want transfer those DataSets to my Website project and bind those datasets to my Report Viewer Control. Is that the right approach? Or do we have to re-create the Datasets and then bind? Can I use Web Services to bind to these reports? Thanks for the input and all help is appreciated.
there is no need to do this what you need to do is to just deploythose reports on the reporting server then create web page and addreport viewer control to it to enable the user view the reports youdeveloped you can see this example to know how to do this
http://www.c-sharpcorner.com/UploadFile/asif.blog/DynamicDataGrouping08042006115320AM/DynamicDataGrouping.aspx
http://www.gotreportviewer.com/
RDC user info
i want to write user info to a table when row deleted on
delete trigger.how can i get user info ?this user is using
query analyzer and connect server via remote desktop
connection. how can i get this user machine name or ip,
thanksThere are system functions that will return that info. Be aware that the
logged in application can optionally set the host information in its
connection string. SQL just reads what the lower levels tell it.
If you want the machine info, use host_name or host_id.
If you want the logged in user info, use system_user or current_user.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
delete trigger.how can i get user info ?this user is using
query analyzer and connect server via remote desktop
connection. how can i get this user machine name or ip,
thanksThere are system functions that will return that info. Be aware that the
logged in application can optionally set the host information in its
connection string. SQL just reads what the lower levels tell it.
If you want the machine info, use host_name or host_id.
If you want the logged in user info, use system_user or current_user.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Wednesday, March 7, 2012
RDA question
i am using RDA to synch data between sql server 2000 and my PDA. i try to
get a parameter list according to the group of a user in my program. my
query to pull is something like that :
"select id ,name from parametertable where groupid in ( select g.id from
grouptable g inner join usertable u on u.groupid = g.id where u.is =
<<userid>>)" with TRACKINGOFF option
this query works well in sql server 2000 and brings what i want. but it
gets all the items of parameter table in the PDA. i do not understand why.
i am new to RDA , is there anything that i miss ?
thanks
try rewriting the query to not use subquery for the RDA pull.
use a join if possible.
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"Yener" <someone@.com.com> wrote in message
news:ORi4O$6wFHA.2620@.TK2MSFTNGP09.phx.gbl...
>i am using RDA to synch data between sql server 2000 and my PDA. i try to
>get a parameter list according to the group of a user in my program. my
>query to pull is something like that :
> "select id ,name from parametertable where groupid in ( select g.id from
> grouptable g inner join usertable u on u.groupid = g.id where u.is =
> <<userid>>)" with TRACKINGOFF option
> this query works well in sql server 2000 and brings what i want. but it
> gets all the items of parameter table in the PDA. i do not understand
> why.
> i am new to RDA , is there anything that i miss ?
> thanks
>
>
|||i found another code at the bottom that pulls the same table again!
i am sorry!!
"Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in message
news:O0%238iFgxFHA.1252@.TK2MSFTNGP09.phx.gbl...
> try rewriting the query to not use subquery for the RDA pull.
> use a join if possible.
> --
> Darren Shaffer
> .NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
> "Yener" <someone@.com.com> wrote in message
> news:ORi4O$6wFHA.2620@.TK2MSFTNGP09.phx.gbl...
>
|||so is your problem solved? not sure if we've helped you here or not.
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"Yener" <someone@.com.com> wrote in message
news:%232QRy5CzFHA.2540@.TK2MSFTNGP09.phx.gbl...
> i found another code at the bottom that pulls the same table again!
> i am sorry!!
> "Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in
> message news:O0%238iFgxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||yes Darren it is solved now.
thanks anyway.
"Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in message
news:eqyDGkMzFHA.3812@.TK2MSFTNGP09.phx.gbl...
> so is your problem solved? not sure if we've helped you here or not.
> --
> Darren Shaffer
> .NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
> "Yener" <someone@.com.com> wrote in message
> news:%232QRy5CzFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
get a parameter list according to the group of a user in my program. my
query to pull is something like that :
"select id ,name from parametertable where groupid in ( select g.id from
grouptable g inner join usertable u on u.groupid = g.id where u.is =
<<userid>>)" with TRACKINGOFF option
this query works well in sql server 2000 and brings what i want. but it
gets all the items of parameter table in the PDA. i do not understand why.
i am new to RDA , is there anything that i miss ?
thanks
try rewriting the query to not use subquery for the RDA pull.
use a join if possible.
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"Yener" <someone@.com.com> wrote in message
news:ORi4O$6wFHA.2620@.TK2MSFTNGP09.phx.gbl...
>i am using RDA to synch data between sql server 2000 and my PDA. i try to
>get a parameter list according to the group of a user in my program. my
>query to pull is something like that :
> "select id ,name from parametertable where groupid in ( select g.id from
> grouptable g inner join usertable u on u.groupid = g.id where u.is =
> <<userid>>)" with TRACKINGOFF option
> this query works well in sql server 2000 and brings what i want. but it
> gets all the items of parameter table in the PDA. i do not understand
> why.
> i am new to RDA , is there anything that i miss ?
> thanks
>
>
|||i found another code at the bottom that pulls the same table again!
i am sorry!!
"Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in message
news:O0%238iFgxFHA.1252@.TK2MSFTNGP09.phx.gbl...
> try rewriting the query to not use subquery for the RDA pull.
> use a join if possible.
> --
> Darren Shaffer
> .NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
> "Yener" <someone@.com.com> wrote in message
> news:ORi4O$6wFHA.2620@.TK2MSFTNGP09.phx.gbl...
>
|||so is your problem solved? not sure if we've helped you here or not.
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"Yener" <someone@.com.com> wrote in message
news:%232QRy5CzFHA.2540@.TK2MSFTNGP09.phx.gbl...
> i found another code at the bottom that pulls the same table again!
> i am sorry!!
> "Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in
> message news:O0%238iFgxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||yes Darren it is solved now.
thanks anyway."Darren Shaffer" <darrenshaffer@.discussions.microsoft.com> wrote in message
news:eqyDGkMzFHA.3812@.TK2MSFTNGP09.phx.gbl...
> so is your problem solved? not sure if we've helped you here or not.
> --
> Darren Shaffer
> .NET Compact Framework MVP
> Principal Architect
> Connected Innovation
> www.connectedinnovation.com
> "Yener" <someone@.com.com> wrote in message
> news:%232QRy5CzFHA.2540@.TK2MSFTNGP09.phx.gbl...
>
RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||I'm not using Replication, but RDA. So, there are no a publication created.
|||Moving to CE forum.|||I solved the problem.
The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).
|||Hi,Fipil
I met the same problem with u.
Glad that you have slove the problem :-)
But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?
Thanks & Best Regards,
Hua wen gui
|||Hi,
I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...
|||So, you did not connect the DB with Login "test" in your PDA program, right?
My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().
meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.
It's a strange problem, any suggestion?
Thanks
|||Yes, you are right. I changed original name of user by 'test' while writting post here.
Did you mean DB on server or db on PDA?
|||The DB on server.
I'd like to pull data from DB Server to PDA with RDA.pull.
But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)
I can not find out the problem.
I'm not sure If the problem on IIS or SQL Server?
Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.
RDA Pull Problem: Command=PULL Hr=80040E4D Login failed for user 'test'
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
Is the test account in the PAL? It may also need to be in the dbo_role in the distribution database.|||I'm not using Replication, but RDA. So, there are no a publication created.
|||Moving to CE forum.|||I solved the problem.
The problem was in login name, I replaced 'g' by 'q' in login name, so this was all the problem :-).
|||Hi,Fipil
I met the same problem with u.
Glad that you have slove the problem :-)
But, I'm not clear what's your mean that "I replaced 'g' by 'q' in login name", Can you explain it detaily?
Thanks & Best Regards,
Hua wen gui
|||Hi,
I made a mistake: I simple put the 'q' character instead of 'g' character to user name field, in my application's login form (on PDA). And, because I implemented remembering of username, the mistake was repeated. Little 'q' is similar to little 'g', so I passed over the mistake :-) and searched for another error...
|||So, you did not connect the DB with Login "test" in your PDA program, right?
My problem is : I can connect the DB with the same connect string in VBA . But, I can not connect DB in PDA program().
meanwhile, I can access http://server/PDA/sscesa20.dll on PDA successfully.
It's a strange problem, any suggestion?
Thanks
|||Yes, you are right. I changed original name of user by 'test' while writting post here.
Did you mean DB on server or db on PDA?
|||The DB on server.
I'd like to pull data from DB Server to PDA with RDA.pull.
But, a error comes out with login error (I can connect the Server DB with the same connection string in VB)
I can not find out the problem.
I'm not sure If the problem on IIS or SQL Server?
Ps, my SQL Server is SQL Server 2005, I can view http://Server/PDA/sscesa20.dll correctly.
Subscribe to:
Comments (Atom)