sql login permissions: users

Showing posts with label users. Show all posts

Friday, March 30, 2012

long running transactions w/ other users needing to read data

I have a very long transaction that runs on the same database that
other users need to use for existing data. I don't care if they see
data from the transaction before it is done and am only using the
transaction because I need a way to roll it back if any errors happen
during the transaction. Unfortunately all tables affected in the long
running transaction are completely locked and nobody else can access
any of the affected tables while it is running. I am using the
transaction isolation level of read uncommitted, which from my limited
understanding of isolation levels is the least strict. What can I do to
prevent this from happening?

Below is the output from sp_who2 and sp_lock while the process is
running and another process is being blocked by it.

SPID Status Login
HostName BlkBy DBName Command CPUTime
DiskIO LastBatch ProgramName SPID
-- ----------
--------------- ---- --
---- ------ --- -- -----
--------- --
1 BACKGROUND sa
. . NULL LAZY WRITER 0 0
06/09 15:42:52 1
2 sleeping sa
. . NULL LOG WRITER 10 0
06/09 15:42:52 2
3 BACKGROUND sa
. . master SIGNAL HANDLER 0 0
06/09 15:42:52 3
4 BACKGROUND sa
. . NULL LOCK MONITOR 0 0
06/09 15:42:52 4
5 BACKGROUND sa
. . master TASK MANAGER 0 5
06/09 15:42:52 5
6 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 6
7 sleeping sa
. . NULL CHECKPOINT SLEEP 0 12
06/09 15:42:52 7
8 BACKGROUND sa
. . master TASK MANAGER 0 2
06/09 15:42:52 8
9 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 9
10 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 10
11 BACKGROUND sa
. . master TASK MANAGER 0 1
06/09 15:42:52 11
12 BACKGROUND sa
. . master TASK MANAGER 0 0
06/09 15:42:52 12
51 sleeping SUPERPABLO\Administrator
SUPERPABLO . PM AWAITING COMMAND 1813
307 06/09 16:10:34 .Net SqlClient Data Provider 51
52 sleeping SUPERPABLO\Administrator
SUPERPABLO 54 PM SELECT 30 5
06/09 16:10:16 .Net SqlClient Data Provider 52
53 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . master SELECT 0 3
06/09 16:09:44 SQL Profiler 53
54 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . PM UPDATE 10095
206 06/09 16:10:02 .Net SqlClient Data Provider 54
56 RUNNABLE SUPERPABLO\Administrator
SUPERPABLO . PM SELECT INTO 151 27
06/09 16:10:33 SQL Query Analyzer 56

(17 row(s) affected)

spid dbid ObjId IndId Type Resource Mode Status
-- -- ---- -- -- ------ --- --
51 5 0 0 DB S GRANT
52 5 0 0 DB S GRANT
52 5 1117963059 4 PAG 1:7401 IS GRANT
52 5 1117963059 4 KEY (5301214e6d62) S WAIT
52 5 1117963059 0 TAB IS GRANT
54 5 1117963059 0 TAB IX GRANT
54 5 1852025829 0 TAB IX GRANT
54 5 1181963287 3 PAG 1:9017 IX GRANT
54 5 1117963059 4 KEY (5301934930a4) X GRANT
54 5 1117963059 3 KEY (530187fc93f3) X GRANT
54 5 1117963059 4 KEY (530154df71eb) X GRANT
54 5 0 0 DB [BULK-OP-LOG] NULL GRANT
54 5 0 0 FIL 2:0:d U GRANT
54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT
54 5 1117963059 2 KEY (1b004a9a6158) X GRANT
54 5 1117963059 2 KEY (1800a435d44a) X GRANT
54 5 1181963287 6 PAG 1:8745 IX GRANT
54 5 1181963287 4 PAG 1:8923 IX GRANT
54 5 1181963287 2 PAG 1:8937 IX GRANT
54 5 1117963059 4 KEY (5301112b0696) X GRANT
54 5 0 0 PAG 1:10889 IX GRANT
54 5 1181963287 5 PAG 1:8859 IX GRANT
54 5 1181963287 6 PAG 1:10888 IX GRANT
54 5 0 0 PAG 1:10891 IX GRANT
54 5 0 0 PAG 1:10893 IX GRANT
54 5 0 0 PAG 1:10892 IX GRANT
54 5 0 0 PAG 1:10894 IX GRANT
54 5 0 0 PAG 1:10882 IX GRANT
54 5 1117963059 3 KEY (530135fbce35) X GRANT
54 5 1117963059 0 RID 1:7387:57 X GRANT
54 5 1117963059 0 RID 1:7387:59 X GRANT
54 5 1117963059 0 RID 1:7387:61 X GRANT
54 5 1117963059 3 KEY (5301406ad2bc) X GRANT
54 5 1117963059 4 PAG 1:7401 IX GRANT
54 5 0 0 PAG 1:7387 IX GRANT
54 5 1117963059 2 PAG 1:7389 IX GRANT
54 5 1117963059 3 PAG 1:7391 IX GRANT
54 5 1117963059 0 RID 1:7387:10 X GRANT
54 5 1117963059 0 RID 1:7387:56 X GRANT
54 5 1117963059 0 RID 1:7387:58 X GRANT
54 5 1117963059 0 RID 1:7387:60 X GRANT
54 5 1117963059 3 KEY (530144afbed8) X GRANT
54 5 1117963059 4 KEY (530115ee6af2) X GRANT
54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT
54 5 1149963173 0 TAB IX GRANT
54 5 1181963287 0 TAB X GRANT
54 5 1117963059 4 KEY (5301d2782bbd) X GRANT
54 5 1117963059 3 KEY (5301015bc9a5) X GRANT
54 5 0 0 DB S GRANT
54 5 0 0 DB [BULK-OP-DB] NULL GRANT
54 5 1117963059 4 KEY (5301501a1d8f) X GRANT
54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT
54 5 1117963059 2 KEY (1a002ffddde0) X GRANT
54 5 0 0 PAG 1:7411 IX GRANT
54 5 1117963059 2 KEY (1900c15268f2) X GRANT
54 5 0 0 PAG 1:10840 IX GRANT
54 5 1181963287 4 PAG 1:10841 IX GRANT
54 5 0 0 PAG 1:10842 IX GRANT
54 5 1117963059 3 KEY (5301059ea5c1) X GRANT
54 5 0 0 PAG 1:10820 IX GRANT
54 5 1181963287 4 PAG 1:10821 IX GRANT
54 5 1181963287 5 PAG 1:10874 IX GRANT
54 5 1181963287 5 PAG 1:10876 IX GRANT
54 5 0 0 PAG 1:10877 IX GRANT
54 5 1181963287 5 PAG 1:10878 IX GRANT
54 5 0 0 PAG 1:10849 IX GRANT
54 5 0 0 PAG 1:10850 IX GRANT
54 5 1117963059 2 KEY (1700f225b712) X GRANT
54 5 1117963059 4 KEY (5301214e6d62) X GRANT
56 5 0 0 DB S GRANT
56 1 85575343 0 TAB IS GRANTAccording to the sp_who2 and sp_lock output, spid54 has the long running
transaction. It has an X lock on Key (5301214e6d62). Spid 52 is trying to
get S lock on the key and got blocked. The read uncommited isolation level
doesn't prevent spid 54 from getting X lock on the key as the X lock is
likely obtained as a result of a modification(insert/delete/update), for
which the transaction can't skip locking. The only way I see to prevent
spid52 from blocking is to apply read uncommited isolation level to spid
52(rather than spid 54), so spid 52 can read dirty uncommitted data modified
by spid 54. Not sure whether your app's semantics allow it though.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"pb648174" <google@.webpaul.net> wrote in message
news:1118351890.044392.321950@.g43g2000cwa.googlegr oups.com...
> I have a very long transaction that runs on the same database that
> other users need to use for existing data. I don't care if they see
> data from the transaction before it is done and am only using the
> transaction because I need a way to roll it back if any errors happen
> during the transaction. Unfortunately all tables affected in the long
> running transaction are completely locked and nobody else can access
> any of the affected tables while it is running. I am using the
> transaction isolation level of read uncommitted, which from my limited
> understanding of isolation levels is the least strict. What can I do to
> prevent this from happening?
> Below is the output from sp_who2 and sp_lock while the process is
> running and another process is being blocked by it.
> SPID Status Login
> HostName BlkBy DBName Command CPUTime
> DiskIO LastBatch ProgramName SPID
> -- ----------
> --------------- ---- --
> ---- ------ --- -- -----
> --------- --
> 1 BACKGROUND sa
> . . NULL LAZY WRITER 0 0
> 06/09 15:42:52 1
> 2 sleeping sa
> . . NULL LOG WRITER 10 0
> 06/09 15:42:52 2
> 3 BACKGROUND sa
> . . master SIGNAL HANDLER 0 0
> 06/09 15:42:52 3
> 4 BACKGROUND sa
> . . NULL LOCK MONITOR 0 0
> 06/09 15:42:52 4
> 5 BACKGROUND sa
> . . master TASK MANAGER 0 5
> 06/09 15:42:52 5
> 6 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 6
> 7 sleeping sa
> . . NULL CHECKPOINT SLEEP 0 12
> 06/09 15:42:52 7
> 8 BACKGROUND sa
> . . master TASK MANAGER 0 2
> 06/09 15:42:52 8
> 9 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 9
> 10 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 10
> 11 BACKGROUND sa
> . . master TASK MANAGER 0 1
> 06/09 15:42:52 11
> 12 BACKGROUND sa
> . . master TASK MANAGER 0 0
> 06/09 15:42:52 12
> 51 sleeping SUPERPABLO\Administrator
> SUPERPABLO . PM AWAITING COMMAND 1813
> 307 06/09 16:10:34 .Net SqlClient Data Provider 51
> 52 sleeping SUPERPABLO\Administrator
> SUPERPABLO 54 PM SELECT 30 5
> 06/09 16:10:16 .Net SqlClient Data Provider 52
> 53 RUNNABLE SUPERPABLO\Administrator
> SUPERPABLO . master SELECT 0 3
> 06/09 16:09:44 SQL Profiler 53
> 54 RUNNABLE SUPERPABLO\Administrator
> SUPERPABLO . PM UPDATE 10095
> 206 06/09 16:10:02 .Net SqlClient Data Provider 54
> 56 RUNNABLE SUPERPABLO\Administrator
> SUPERPABLO . PM SELECT INTO 151 27
> 06/09 16:10:33 SQL Query Analyzer 56
> (17 row(s) affected)
> spid dbid ObjId IndId Type Resource Mode Status
> -- -- ---- -- -- ------ --- --
> 51 5 0 0 DB S GRANT
> 52 5 0 0 DB S GRANT
> 52 5 1117963059 4 PAG 1:7401 IS GRANT
> 52 5 1117963059 4 KEY (5301214e6d62) S WAIT
> 52 5 1117963059 0 TAB IS GRANT
> 54 5 1117963059 0 TAB IX GRANT
> 54 5 1852025829 0 TAB IX GRANT
> 54 5 1181963287 3 PAG 1:9017 IX GRANT
> 54 5 1117963059 4 KEY (5301934930a4) X GRANT
> 54 5 1117963059 3 KEY (530187fc93f3) X GRANT
> 54 5 1117963059 4 KEY (530154df71eb) X GRANT
> 54 5 0 0 DB [BULK-OP-LOG] NULL GRANT
> 54 5 0 0 FIL 2:0:d U GRANT
> 54 5 1117963059 2 KEY (1d0096c50a7d) X GRANT
> 54 5 1117963059 2 KEY (1b004a9a6158) X GRANT
> 54 5 1117963059 2 KEY (1800a435d44a) X GRANT
> 54 5 1181963287 6 PAG 1:8745 IX GRANT
> 54 5 1181963287 4 PAG 1:8923 IX GRANT
> 54 5 1181963287 2 PAG 1:8937 IX GRANT
> 54 5 1117963059 4 KEY (5301112b0696) X GRANT
> 54 5 0 0 PAG 1:10889 IX GRANT
> 54 5 1181963287 5 PAG 1:8859 IX GRANT
> 54 5 1181963287 6 PAG 1:10888 IX GRANT
> 54 5 0 0 PAG 1:10891 IX GRANT
> 54 5 0 0 PAG 1:10893 IX GRANT
> 54 5 0 0 PAG 1:10892 IX GRANT
> 54 5 0 0 PAG 1:10894 IX GRANT
> 54 5 0 0 PAG 1:10882 IX GRANT
> 54 5 1117963059 3 KEY (530135fbce35) X GRANT
> 54 5 1117963059 0 RID 1:7387:57 X GRANT
> 54 5 1117963059 0 RID 1:7387:59 X GRANT
> 54 5 1117963059 0 RID 1:7387:61 X GRANT
> 54 5 1117963059 3 KEY (5301406ad2bc) X GRANT
> 54 5 1117963059 4 PAG 1:7401 IX GRANT
> 54 5 0 0 PAG 1:7387 IX GRANT
> 54 5 1117963059 2 PAG 1:7389 IX GRANT
> 54 5 1117963059 3 PAG 1:7391 IX GRANT
> 54 5 1117963059 0 RID 1:7387:10 X GRANT
> 54 5 1117963059 0 RID 1:7387:56 X GRANT
> 54 5 1117963059 0 RID 1:7387:58 X GRANT
> 54 5 1117963059 0 RID 1:7387:60 X GRANT
> 54 5 1117963059 3 KEY (530144afbed8) X GRANT
> 54 5 1117963059 4 KEY (530115ee6af2) X GRANT
> 54 5 1117963059 3 KEY (5301c6cd88ea) X GRANT
> 54 5 1149963173 0 TAB IX GRANT
> 54 5 1181963287 0 TAB X GRANT
> 54 5 1117963059 4 KEY (5301d2782bbd) X GRANT
> 54 5 1117963059 3 KEY (5301015bc9a5) X GRANT
> 54 5 0 0 DB S GRANT
> 54 5 0 0 DB [BULK-OP-DB] NULL GRANT
> 54 5 1117963059 4 KEY (5301501a1d8f) X GRANT
> 54 5 1117963059 2 KEY (1c00f3a2b6c5) X GRANT
> 54 5 1117963059 2 KEY (1a002ffddde0) X GRANT
> 54 5 0 0 PAG 1:7411 IX GRANT
> 54 5 1117963059 2 KEY (1900c15268f2) X GRANT
> 54 5 0 0 PAG 1:10840 IX GRANT
> 54 5 1181963287 4 PAG 1:10841 IX GRANT
> 54 5 0 0 PAG 1:10842 IX GRANT
> 54 5 1117963059 3 KEY (5301059ea5c1) X GRANT
> 54 5 0 0 PAG 1:10820 IX GRANT
> 54 5 1181963287 4 PAG 1:10821 IX GRANT
> 54 5 1181963287 5 PAG 1:10874 IX GRANT
> 54 5 1181963287 5 PAG 1:10876 IX GRANT
> 54 5 0 0 PAG 1:10877 IX GRANT
> 54 5 1181963287 5 PAG 1:10878 IX GRANT
> 54 5 0 0 PAG 1:10849 IX GRANT
> 54 5 0 0 PAG 1:10850 IX GRANT
> 54 5 1117963059 2 KEY (1700f225b712) X GRANT
> 54 5 1117963059 4 KEY (5301214e6d62) X GRANT
> 56 5 0 0 DB S GRANT
> 56 1 85575343 0 TAB IS GRANT|||The thing is though, the long running transaction is the only
transaction running. All the other processes are just running non
transactional queries.|||I've cheated with large INSERT statements by inserting blocks of them
in a batch inside a transaction, with a WAITFOR DELAY of a few seconds.
This makes the individual transactions shorter, and allows the other
statements to sneak in and get some work done while doing it. I've
found that inserting 5 batches of of increasing percentage sizes (20,
25, 30, 50, 100) usually ends up inserting near equivelent batch sizes.

The psuedo-code for this method would be something like the following:

BEGIN TRANSACTION

INSERT INTO holdingTable
SELECT PrimaryKey
FROM Table

-- first 2000 records of 10000 records
INSERT INTO DestinationTable
SELECT TOP 20 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey

DELETE
FROM HoldingTable
WHERE PrimaryKey IN (SELECT TOP 20 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY)

--pause for 10 seconds
WAITFOR DELAY '00:00:10'

--first 2000 records of remaining 8000 records
INSERT INTO DestinationTable
SELECT TOP 25 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey

DELETE
FROM HoldingTable
WHERE PrimaryKey IN (SELECT TOP 25 PERCENT PrimaryKey
FROM HoldingTable ORDER BY Primary KEY)

WAITFOR DELAY '00:00:10'

....

--remaining records
INSERT INTO DestinationTable
SELECT TOP 100 PERCENT Cols
FROM SourceTable JOIN HoldingTable ON a=b
ORDER BY HoldingTable PrimaryKey

drop HoldingTable --assumes it's a temp table or table variable

COMMIT TRANSACTION

A similar concept should work for UPDATES.

There may be other solutions out there; this works for me.|||pb648174 wrote:
> The thing is though, the long running transaction is the only
> transaction running. All the other processes are just running non
> transactional queries.

If you read the BOL section "SET IMPLICIT_TRANSACTIONS", You should be
able to see that *every* statement is part of a transaction - there are
no "non transactional" queries.

What actually happens when you execute a query when there is no active
transaction is that a new transaction is started. When the query
completes, the behaviour is affected by the "IMPLICIT_TRANSACTIONS"
settings. When it is OFF (the default), the transaction is
automatically committed, provided the query caused no error. When it is
ON, the transaction is kept open, and must be manually committed at a
later stage.

So, on to your problem. You *may* be able to fix it by issueing the
"SET TRANSACTION ISOLATION LEVEL" statement on each of the other
processes connections. by setting it to READ UNCOMMITTED, this will
affect every transaction which is started by this connection (including
these automatic transactions which your statements are
opening/committing).

This will mean that each of these processes may see data in any
imaginable (read: illegal) state. If you are sure that it is safe for
these processes to see (and process) such data, then this may be the
way to go.

HTH,

Damien|||This large copy is happening from application code and involved 30 or
40 separate stored procedure being called. It is being done
asynchronously via a c# web app and there is a pause of 2 seconds
before every copy command. Unfortunately this two second pause doesn't
help because of the locking situation, it just makes everything block
for longer.

Isn't this a somewhat common scenario? What is the standard way around
this situation? Can somebody tell me why the entire table is being
locked instead of just the new records? I have no problem with new
records being locked, but I do not want it to lock the records it is
reading or the entire table.

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE
GO|||pb648174 wrote:
> The thing is though, the long running transaction is the only
> transaction running. All the other processes are just running non
> transactional queries.

If you read the BOL section "SET IMPLICIT_TRANSACTIONS", You should be
able to see that *every* statement is part of a transaction - there are
no "non transactional" queries.

HTH,

Would disaster ensue if I submitted the following command?
SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE
GO|||pb648174 (google@.webpaul.net) writes:
> This large copy is happening from application code and involved 30 or
> 40 separate stored procedure being called. It is being done
> asynchronously via a c# web app and there is a pause of 2 seconds
> before every copy command. Unfortunately this two second pause doesn't
> help because of the locking situation, it just makes everything block
> for longer.
> Isn't this a somewhat common scenario? What is the standard way around
> this situation? Can somebody tell me why the entire table is being
> locked instead of just the new records? I have no problem with new
> records being locked, but I do not want it to lock the records it is
> reading or the entire table.
> Would disaster ensue if I submitted the following command?
> SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE

It would not have any effect.

The table you are inserting into is *not* completely locked. You posted
an output from sp_lock. According to this output, spid 54 holds an
exclusive table lock on table 1181963287, but that does not seem to
the main theatre for your problem. (You can use
"SELECT object_name(1181963287)" to see which table this is.)

Instead, the main part of the show appears to be table 1117963059, and
spid 54 does not any table locks on this table. But it does hold locks
on all newly inserted rows, as well as all new inserted index nodes.
Process 52 is blocked by spid 54, and this is why:

52 5 1117963059 4 KEY (5301214e6d62) S WAIT

Spid 52 is trying to get a shared lock on an index key, but is blocked.

Assume that the query spid 52 has submitted is "show how many items of
widget X we sold last week", and the optimizer decides to use the
non-clustered index over widget_id to access the sales numbers. And
among the new rows you insert, there are rows with widget_id in question.
When spid 52 tries to access those index nodes, it will be blocked.

So while you don't get a table locked, it is not as simple that other
processes can just read the existing data, and don't bother about the
new data.

The best way would be look into how to shorten the transaction length.
It sounds as if row are being inserted one-by-one, in which case there
are lots of possibilities for improvements.

Another possibility is insert the data into a staging table, and the
insert with one big INSERT statement at the end. But if that is many
rows, that could still block for considerable time.

A further development is to use partitioned views. Here, too, you copy
the data into an empty table, that no other process sees. The processes
that reads data, access the view, not the table. Once the table has been
loaded, you change the view definition to include the new table.

Then you can of course, use READ UNCOMMITTED for the readers, but then
may get some funky results that is not consistent. (READ UNCOMMITTED
on the writer has no effect.) A better alternative may be READPAST,
in which cases locked rows are simply skipped. But this is only a locking
hint, and is not settable as a transaction level.

In SQL 2005, you would probably use the new SNAPSHOT isolattion level,
in which case the readers would see the data as it was before the long
transaction started.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> This large copy is happening from application code and involved 30 or
> 40 separate stored procedure being called. It is being done
> asynchronously via a c# web app and there is a pause of 2 seconds
> before every copy command. Unfortunately this two second pause doesn't
> help because of the locking situation, it just makes everything block
> for longer.
> Isn't this a somewhat common scenario? What is the standard way around
> this situation? Can somebody tell me why the entire table is being
> locked instead of just the new records? I have no problem with new
> records being locked, but I do not want it to lock the records it is
> reading or the entire table.
> Would disaster ensue if I submitted the following command?
> SP_INDEXOPTION 'table_name', 'AllowTableLocks', FALSE

It would not have any effect.

52 5 1117963059 4 KEY (5301214e6d62) S WAIT

Spid 52 is trying to get a shared lock on an index key, but is blocked.

So while you don't get a table locked, it is not as simple that other
processes can just read the existing data, and don't bother about the
new data.

The best way would be look into how to shorten the transaction length.
It sounds as if row are being inserted one-by-one, in which case there
are lots of possibilities for improvements.

Another possibility is insert the data into a staging table, and the
insert with one big INSERT statement at the end. But if that is many
rows, that could still block for considerable time.

In SQL 2005, you would probably use the new SNAPSHOT isolattion level,
in which case the readers would see the data as it was before the long
transaction started.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
some work on the indexes in order to fix it? Would changing to a
clustered index make it less likely to block?

The rows aren't being inserted one row at a time, but they are being
done one "project" at a time(maybe a few thousand records), with pretty
standard Insert Into..Select statements. I have my application pausing
for a few seconds between projects to try and reduce stress on the
server and give other processes a chance for freedom, but from what you
are saying, perhaps I need to remove the wait times in order to get the
transaction over as quickly as possible.|||Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
some work on the indexes in order to fix it? Would changing to a
clustered index make it less likely to block?

The rows aren't being inserted one row at a time, but they are being
done one "project" at a time(maybe a few thousand records), with pretty
standard Insert Into..Select statements. I have my application pausing
for a few seconds between projects to try and reduce stress on the
server and give other processes a chance for freedom, but from what you
are saying, perhaps I need to remove the wait times in order to get the
transaction over as quickly as possible.|||On 9 Jun 2005 14:18:10 -0700, "pb648174" <google@.webpaul.net> wrote:

>I have a very long transaction that runs on the same database that
>other users need to use for existing data. I don't care if they see
>data from the transaction before it is done and am only using the
>transaction because I need a way to roll it back if any errors happen
>during the transaction. Unfortunately all tables affected in the long
>running transaction are completely locked and nobody else can access
>any of the affected tables while it is running. I am using the
>transaction isolation level of read uncommitted, which from my limited
>understanding of isolation levels is the least strict. What can I do to
>prevent this from happening?

Personally, I don't abide long-running database transactions in my
applications. If the issue comes up, I reingineer until the need for the
long-running transaction goes away.

Here's one approach I've used...
1. For each table that will be affected, add 2 links to batch records, one for
initial, and one for final.
2. Add a table of batches with a batch ID, and a status that may be pending or
completed.
3. For each batch process, create a new batch record with a status of pending.
4. When writing to the database, point the initial batch of each new record to
the batch record, and point the final batch of each deleted record to the
batch record. Do not modify existing records - instead, add a new modifed
copy, and finalize the old copy.
5. Once all batch updates are completed, change the batch record status from
pending to completed.

When querying the data, to see only data that is current, simply join to the
batch table, and exclude any records that have final batch links to a
completed batch record. Every once in a while, purge these outdated records,
so they don't pile up, and slow down the system.

If a transaction fails, you can roll back by deleting the records with initial
batch references to your batch record, and set any final batch references to
your batch record back to Null, then delete your batch record.

This approach only works if there can only be one batch processor at a time
affecting a particular group of tables, but it has the benefit of not
requiring server transactions to be maintained for long periods of time. A
batch could take several days and have no negative impact on anything. You
could even halt the batch, and continue it on a different meachine if
necessary.|||On 9 Jun 2005 14:18:10 -0700, "pb648174" <google@.webpaul.net> wrote:

Personally, I don't abide long-running database transactions in my
applications. If the issue comes up, I reingineer until the need for the
long-running transaction goes away.

This approach only works if there can only be one batch processor at a time
affecting a particular group of tables, but it has the benefit of not
requiring server transactions to be maintained for long periods of time. A
batch could take several days and have no negative impact on anything. You
could even halt the batch, and continue it on a different meachine if
necessary.|||pb648174 (google@.webpaul.net) writes:
> Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
> some work on the indexes in order to fix it? Would changing to a
> clustered index make it less likely to block?

I sort of assumed that all new rows were inserted at the end of the
clustered index. But if the rows you insert are not aligned with the
clustered index, then the problem becomes a lot worse. Any process
that finds itself in need ot a table scan would be blocked.

> The rows aren't being inserted one row at a time, but they are being
> done one "project" at a time(maybe a few thousand records), with pretty
> standard Insert Into..Select statements. I have my application pausing
> for a few seconds between projects to try and reduce stress on the
> server and give other processes a chance for freedom, but from what you
> are saying, perhaps I need to remove the wait times in order to get the
> transaction over as quickly as possible.

Had you committed after each batch, the pause could make some sense. But
if you don't commit until the end, then you should get away with those
pauses.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> Wow, that helped clear things up quite a bit, thanks. Can I perhaps do
> some work on the indexes in order to fix it? Would changing to a
> clustered index make it less likely to block?

Had you committed after each batch, the pause could make some sense. But
if you don't commit until the end, then you should get away with those
pauses.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.|||The only thing about taking out the pauses is that it hammers on the
database so hard, it pegs the CPU.. With the pauses other processes
have a chance to go forward, but with this locking it doesn't seem to
matter.|||Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.|||Isn't this "batch" approach sort of ridiculous, given that databases
are supposed to be able to run transactions?? We have hundreds of
tables and the above approach would be extremely onerous. I don't think
it would work anyway since there are identity columns in most of the
tables which need to match up between parent and child relationships.
If somebody inserted a single record while the transaction was running,
that would hose the whole process.|||This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?|||This is interesting - adding a clustered index on the identity column
fixed the previous problem, so I picked one module (which has about 5
or 6 tables) to see if I could free up that module while the copy
process was running. I went and put clustered indexes on all the
identity columns of those tables, but now another table has a locking
problem, although this time, not on the index. The output from that
line from sp_lock is below:

54 8 2101582525 1 PAG 1:26568 S WAIT

So now it is a page lock, which from what I've read is a group of
records. That seems resonable given that I am copying large amounts of
data, but why is it stopping my other process?|||pb648174 (google@.webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU..

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

CPU:s are humans. Just keep them working!

(But, OK, if you see a puff of smoke, it's probably time for a pause.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU.. With the pauses other processes
> have a chance to go forward, but with this locking it doesn't seem to
> matter.

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pb648174 (google@.webpaul.net) writes:
> This is interesting - adding a clustered index on the identity column
> fixed the previous problem, so I picked one module (which has about 5
> or 6 tables) to see if I could free up that module while the copy
> process was running. I went and put clustered indexes on all the
> identity columns of those tables, but now another table has a locking
> problem, although this time, not on the index. The output from that
> line from sp_lock is below:
> 54 8 2101582525 1 PAG 1:26568 S WAIT
> So now it is a page lock, which from what I've read is a group of
> records. That seems resonable given that I am copying large amounts of
> data, but why is it stopping my other process?

Lacking telepathic abilities, I can't say why.

What you could try is to use aba_lockinfo, which you find on my web
site, http://www.sommarskog.se/sqlutil/aba_lockinfo.html. This procedure
give you an overview active process, which object they lock, and also
current statements. This gives a little better idea of what is going
on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Lacking telepathic abilities, I can't say why.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have no idea if this will help or not...
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10145 WITH(NOLOCK) ON
view_TPSEnum_10145.nIndex = [tblObjectType5001_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.|||I have no idea if this will help or not...
A vendor gave me a giant sql select that has 200 fields and 25 joins.
The sample of the join:
LEFT OUTER JOIN view_TPSEnum_10145 WITH(NOLOCK) ON
view_TPSEnum_10145.nIndex = [tblObjectType5001_1].[FldNumeric21251]
I'm told the WITH(NOLOCK) wont lock the source table *shrug*. Read the
docs that it allows dirty reads and is used only for selects.

Friday, March 23, 2012

logshipping load failure

Hi Guys,
I implemented log shipping but if database is open during
load the restore is failing.
i think i selected "terminate users in database" option.
how i can confirm whether i selected this option? and if
not how can i change this.
THNKS
BIJU
Biju,
the easiest way to check and change this property is to edit the maintenance
plan, goto the logshipping tab and edit, on the initialize tab select
'Terminate Users in Database'.
HTH,
Paul Ibison

Wednesday, March 21, 2012

Logon window - Another one

Hi all,
I'm experiencing problems that so many people already had...except for
the fact, that no given solution worked for me.
Some of my users are getting windows-logon-popups, when they click on a
report. Admins have no troubles, just *some* users.
I tried Adding them in the virtual server security dialog, didn't work.
I tried the IE settings, didn't solve the problem.
Does anybody have other ideas?
Regards,
Bj=F6rnAdd the report server to the INTRANET zone (you want it to pass the users
authentication credentials). The INTRANET zone by default does this.
When I say add it to the INTRANET zone, be sure to add the fully qualified
name, not just the server name.
i.e. http://reportserver.mydomain
=-Chris
"Bjorn" <bjorn.vaessen@.gmail.com> wrote in message
news:1161766822.164928.118120@.h48g2000cwc.googlegroups.com...
Hi all,
I'm experiencing problems that so many people already had...except for
the fact, that no given solution worked for me.
Some of my users are getting windows-logon-popups, when they click on a
report. Admins have no troubles, just *some* users.
I tried Adding them in the virtual server security dialog, didn't work.
I tried the IE settings, didn't solve the problem.
Does anybody have other ideas?
Regards,
Björn

Monday, March 19, 2012

Logon Failed and Subscriptions

Anyone know why I keep getting a logon failed when my email subscription
runs? I have used all users imaginable on my data source and none seems to
work.
SQLServer Agent is running and my users have RSExec role enabled.Is the logon failure an SMTP error? Perhaps you do not have your SMTP
settings correct. Can you post the error message?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
> Anyone know why I keep getting a logon failed when my email subscription
> runs? I have used all users imaginable on my data source and none seems to
> work.
> SQLServer Agent is running and my users have RSExec role enabled.
>|||Below is my Report services file errors. But to add to by question.
I created a system user named reportuser. reportUser has access to Report
Server and the database I am making my query. this user has RSExec
permissions. This user is also setup in Report Manager to store credential
securely in Report Server on my shared datasource. I don't know what I have
to do next. Can you help me?
Report Services file is as follows:
ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07:: EventPolling
processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07:: EventPolling
processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating Time
based subscription notification for subscription:
7d229c62-b339-471b-8622-358857f2870a
ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005 3:17:08
PM.
ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08:: EventPolling
finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
NotificationPolling processing item 70a496c4-b071-44c5-8f7d-f2e063dc41dc
ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
Initializing EnableIntegratedSecurity to 'True' as specified in Server
system properties.
ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon
failed., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Logon
failed. --> System.Runtime.InteropServices.COMException (0x8007052E): Logon
failure: unknown user name or bad password.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
String pDomain, String pPassword, Boolean bTryRemote, IntPtr& pImpToken)
at
Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String userName, String userPwd, String domain)
-- End of inner exception stack trace --
ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e ERROR:
Error occured processing notification. Logon failed.
ReportingServicesService!notification!1994!12/09/2005-15:17:12::
Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success: False,
Status: Logon failed., DeliveryExtension: Report Server Email, Report:
Incomplete Loan Applications, Attempt 0
ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
NotificationPolling finished processing item
70a496c4-b071-44c5-8f7d-f2e063dc41dc
ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO: Cleaned
0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
"Daniel Reib [MSFT]" wrote:
> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
> settings correct. Can you post the error message?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
> > Anyone know why I keep getting a logon failed when my email subscription
> > runs? I have used all users imaginable on my data source and none seems to
> > work.
> > SQLServer Agent is running and my users have RSExec role enabled.
> >
> >
>
>|||For some reason the system is not able to use the username and password that
you supplied. RS is attempting to logon using these credentials. Is this a
valid user on the computer that RS is installed on? Have you tried to
logon to that computer with these credentials? If all of these things are
correct then I would ensure that you have correctly set the password in the
datasource.
Can you view the report just fine? I would think you could not view it as
well.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:F18FFFED-2D43-4AAA-970E-BDDE5164A79D@.microsoft.com...
> Below is my Report services file errors. But to add to by question.
> I created a system user named reportuser. reportUser has access to Report
> Server and the database I am making my query. this user has RSExec
> permissions. This user is also setup in Report Manager to store credential
> securely in Report Server on my shared datasource. I don't know what I
> have
> to do next. Can you help me?
> Report Services file is as follows:
> ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07:: EventPolling
> processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07:: EventPolling
> processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
> ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating Time
> based subscription notification for subscription:
> 7d229c62-b339-471b-8622-358857f2870a
> ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005
> 3:17:08
> PM.
> ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08:: EventPolling
> finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
> NotificationPolling processing 1 more items. 1 Total items in internal
> queue.
> ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
> NotificationPolling processing item 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
> Initializing EnableIntegratedSecurity to 'True' as specified in Server
> system properties.
> ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
> Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> Logon
> failed., ;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> Logon
> failed. --> System.Runtime.InteropServices.COMException (0x8007052E):
> Logon
> failure: unknown user name or bad password.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
> String pDomain, String pPassword, Boolean bTryRemote, IntPtr& pImpToken)
> at
> Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String
> userName, String userPwd, String domain)
> -- End of inner exception stack trace --
> ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e ERROR:
> Error occured processing notification. Logon failed.
> ReportingServicesService!notification!1994!12/09/2005-15:17:12::
> Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success:
> False,
> Status: Logon failed., DeliveryExtension: Report Server Email, Report:
> Incomplete Loan Applications, Attempt 0
> ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
> NotificationPolling finished processing item
> 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO:
> Cleaned
> 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> "Daniel Reib [MSFT]" wrote:
>> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
>> settings correct. Can you post the error message?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
>> > Anyone know why I keep getting a logon failed when my email
>> > subscription
>> > runs? I have used all users imaginable on my data source and none seems
>> > to
>> > work.
>> > SQLServer Agent is running and my users have RSExec role enabled.
>> >
>> >
>>|||The rptuser I set up is a SQL user not a domain user. It is able to connect
to the server and view the report, but doesn't email and I get a logon failed
error. I do see the Job ran successfully under the SQLServer Agent jobs in
Enterprise manager. Does The rpt user I set up have to be a domain user?
"Daniel Reib [MSFT]" wrote:
> For some reason the system is not able to use the username and password that
> you supplied. RS is attempting to logon using these credentials. Is this a
> valid user on the computer that RS is installed on? Have you tried to
> logon to that computer with these credentials? If all of these things are
> correct then I would ensure that you have correctly set the password in the
> datasource.
>
> Can you view the report just fine? I would think you could not view it as
> well.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:F18FFFED-2D43-4AAA-970E-BDDE5164A79D@.microsoft.com...
> > Below is my Report services file errors. But to add to by question.
> > I created a system user named reportuser. reportUser has access to Report
> > Server and the database I am making my query. this user has RSExec
> > permissions. This user is also setup in Report Manager to store credential
> > securely in Report Server on my shared datasource. I don't know what I
> > have
> > to do next. Can you help me?
> >
> > Report Services file is as follows:
> >
> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07:: EventPolling
> > processing 1 more items. 1 Total items in internal queue.
> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07:: EventPolling
> > processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
> > ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating Time
> > based subscription notification for subscription:
> > 7d229c62-b339-471b-8622-358857f2870a
> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005
> > 3:17:08
> > PM.
> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08:: EventPolling
> > finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
> > NotificationPolling processing 1 more items. 1 Total items in internal
> > queue.
> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
> > NotificationPolling processing item 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> > ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
> > Initializing EnableIntegratedSecurity to 'True' as specified in Server
> > system properties.
> > ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
> > Throwing
> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> > Logon
> > failed., ;
> > Info:
> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
> > Logon
> > failed. --> System.Runtime.InteropServices.COMException (0x8007052E):
> > Logon
> > failure: unknown user name or bad password.
> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> > errorCode, IntPtr errorInfo)
> > at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
> > String pDomain, String pPassword, Boolean bTryRemote, IntPtr& pImpToken)
> > at
> > Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String
> > userName, String userPwd, String domain)
> > -- End of inner exception stack trace --
> > ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e ERROR:
> > Error occured processing notification. Logon failed.
> > ReportingServicesService!notification!1994!12/09/2005-15:17:12::
> > Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success:
> > False,
> > Status: Logon failed., DeliveryExtension: Report Server Email, Report:
> > Incomplete Loan Applications, Attempt 0
> > ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
> > NotificationPolling finished processing item
> > 70a496c4-b071-44c5-8f7d-f2e063dc41dc
> > ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO:
> > Cleaned
> > 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
> >> settings correct. Can you post the error message?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> >> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
> >> > Anyone know why I keep getting a logon failed when my email
> >> > subscription
> >> > runs? I have used all users imaginable on my data source and none seems
> >> > to
> >> > work.
> >> > SQLServer Agent is running and my users have RSExec role enabled.
> >> >
> >> >
> >>
> >>
> >>
>
>|||No report user does not need to be a domain user. You may want to use
rsconfig.exe to set an unattended account. Perhaps this account was set
incorrectly.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:BC2653B2-59A1-4141-ADD6-CB203A7F34F3@.microsoft.com...
> The rptuser I set up is a SQL user not a domain user. It is able to
> connect
> to the server and view the report, but doesn't email and I get a logon
> failed
> error. I do see the Job ran successfully under the SQLServer Agent jobs in
> Enterprise manager. Does The rpt user I set up have to be a domain user?
> "Daniel Reib [MSFT]" wrote:
>> For some reason the system is not able to use the username and password
>> that
>> you supplied. RS is attempting to logon using these credentials. Is
>> this a
>> valid user on the computer that RS is installed on? Have you tried to
>> logon to that computer with these credentials? If all of these things
>> are
>> correct then I would ensure that you have correctly set the password in
>> the
>> datasource.
>>
>> Can you view the report just fine? I would think you could not view it
>> as
>> well.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:F18FFFED-2D43-4AAA-970E-BDDE5164A79D@.microsoft.com...
>> > Below is my Report services file errors. But to add to by question.
>> > I created a system user named reportuser. reportUser has access to
>> > Report
>> > Server and the database I am making my query. this user has RSExec
>> > permissions. This user is also setup in Report Manager to store
>> > credential
>> > securely in Report Server on my shared datasource. I don't know what I
>> > have
>> > to do next. Can you help me?
>> >
>> > Report Services file is as follows:
>> >
>> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:07::
>> > EventPolling
>> > processing 1 more items. 1 Total items in internal queue.
>> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:07::
>> > EventPolling
>> > processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
>> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
>> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 executed at 12/9/2005 3:17:08 PM.
>> > ReportingServicesService!schedule!1994!12/9/2005-15:17:08:: Creating
>> > Time
>> > based subscription notification for subscription:
>> > 7d229c62-b339-471b-8622-358857f2870a
>> > ReportingServicesService!library!1994!12/9/2005-15:17:08:: Schedule
>> > e4d454cf-4bae-44f6-bd2b-32b14ac12347 execution completed at 12/9/2005
>> > 3:17:08
>> > PM.
>> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
>> > EventPolling
>> > finished processing item 3c2cd2c9-3606-4fd4-9496-a755e9c893d6
>> > ReportingServicesService!dbpolling!16e4!12/9/2005-15:17:08::
>> > NotificationPolling processing 1 more items. 1 Total items in internal
>> > queue.
>> > ReportingServicesService!dbpolling!1994!12/9/2005-15:17:08::
>> > NotificationPolling processing item
>> > 70a496c4-b071-44c5-8f7d-f2e063dc41dc
>> > ReportingServicesService!library!1994!12/09/2005-15:17:11:: i INFO:
>> > Initializing EnableIntegratedSecurity to 'True' as specified in Server
>> > system properties.
>> > ReportingServicesService!library!1994!12/09/2005-15:17:12:: e ERROR:
>> > Throwing
>> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
>> > Logon
>> > failed., ;
>> > Info:
>> > Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException:
>> > Logon
>> > failed. --> System.Runtime.InteropServices.COMException (0x8007052E):
>> > Logon
>> > failure: unknown user name or bad password.
>> > at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
>> > errorCode, IntPtr errorInfo)
>> > at RSRemoteRpcClient.RemoteLogon.GetRemoteImpToken(String pUserName,
>> > String pDomain, String pPassword, Boolean bTryRemote, IntPtr&
>> > pImpToken)
>> > at
>> > Microsoft.ReportingServices.Diagnostics.DatasourceRuntimeContext.MakeUserToken(String
>> > userName, String userPwd, String domain)
>> > -- End of inner exception stack trace --
>> > ReportingServicesService!notification!1994!12/09/2005-15:17:12:: e
>> > ERROR:
>> > Error occured processing notification. Logon failed.
>> > ReportingServicesService!notification!1994!12/09/2005-15:17:12::
>> > Notification 70a496c4-b071-44c5-8f7d-f2e063dc41dc completed. Success:
>> > False,
>> > Status: Logon failed., DeliveryExtension: Report Server Email, Report:
>> > Incomplete Loan Applications, Attempt 0
>> > ReportingServicesService!dbpolling!1994!12/09/2005-15:17:12::
>> > NotificationPolling finished processing item
>> > 70a496c4-b071-44c5-8f7d-f2e063dc41dc
>> > ReportingServicesService!library!1994!12/09/2005-15:19:04:: i INFO:
>> > Cleaned
>> > 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots,
>> > 0
>> > chunks, 0 running jobs
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Is the logon failure an SMTP error? Perhaps you do not have your SMTP
>> >> settings correct. Can you post the error message?
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> >> news:6B033AF7-7476-4DDC-9873-BA6F2A6F1CF9@.microsoft.com...
>> >> > Anyone know why I keep getting a logon failed when my email
>> >> > subscription
>> >> > runs? I have used all users imaginable on my data source and none
>> >> > seems
>> >> > to
>> >> > work.
>> >> > SQLServer Agent is running and my users have RSExec role enabled.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>

Monday, March 12, 2012

Logins, Users, Roles, Schemas

Hi everyone,
I have just migrating my database From SQL2K to SQL2005. Users, Roles
and Schemas were all migrated. No issues there. Logins, on the other
hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
orphans, correct?
If I re-create the login and assigning the login to the database, it
will give an error saying that the user already exists. So, I delete the
User, then it prompts me that I have a schema associated with it, so I
delete the schema, then create the user. Roles have not been touched.
Must I also update the roles? and I'm not sure what schemas are in
comparison to SQL 2000.
Please advise of a better way to deal with users, logins, roles and
schemas.
Thanks,
Audrey
*** Sent via Developersdex http://www.codecomments.com ***Perhaps these resources will help you.
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> I have just migrating my database From SQL2K to SQL2005. Users, Roles
> and Schemas were all migrated. No issues there. Logins, on the other
> hand, did not get migrated. Thus, I believe making Users, Roles, Schemas
> orphans, correct?
> If I re-create the login and assigning the login to the database, it
> will give an error saying that the user already exists. So, I delete the
> User, then it prompts me that I have a schema associated with it, so I
> delete the schema, then create the user. Roles have not been touched.
> Must I also update the roles? and I'm not sure what schemas are in
> comparison to SQL 2000.
> Please advise of a better way to deal with users, logins, roles and
> schemas.
> Thanks,
> Audrey
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Thanks for these articles. I have a slightly different issue. We moved a SQL
2000 database from server "A" to server "B" and there are now orphaned
users. They are actually old admins, etc., that we don't even want any more.
How do you delete these from a database? Is it as simple as just deleting
the logins from the database?
I haven't found anything on this topic and I would like some assurance that
it should work without croaking the database.
Thanks,
Ray
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Perhaps these resources will help you.
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:egfRrmTzGHA.4580@.TK2MSFTNGP05.phx.gbl...
>|||Yes, if they are truly 'orphans', you can just delete them.
However, if they 'own' any objects, the deletion will fail. You will then
have to find those objects, and change the ownership to [dbo] before try
ing
the deletion again.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Do sp_change_users_login 'report' to get a list of orphans before you decide
to do anything.
Also, check out this kb for more info.
http://support.microsoft.com/kb/274188
-oj
"Ray" <no@.repliesplease.com> wrote in message
news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
> Thanks for these articles. I have a slightly different issue. We moved a
> SQL 2000 database from server "A" to server "B" and there are now orphaned
> users. They are actually old admins, etc., that we don't even want any
> more.
> How do you delete these from a database? Is it as simple as just deleting
> the logins from the database?
> I haven't found anything on this topic and I would like some assurance
> that it should work without croaking the database.
> Thanks,
> Ray
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23afucvTzGHA.2208@.TK2MSFTNGP03.phx.gbl...
>|||Thanks to the both of you.
Ray
"oj" <nospam_ojngo@.home.com> wrote in message
news:eSR$4wgzGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Do sp_change_users_login 'report' to get a list of orphans before you
> decide to do anything.
> Also, check out this kb for more info.
> http://support.microsoft.com/kb/274188
>
> --
> -oj
>
> "Ray" <no@.repliesplease.com> wrote in message
> news:%23EFW%23FczGHA.3704@.TK2MSFTNGP02.phx.gbl...
>

Logins to the website doesnt work after DB backing up and restoring

Hi all, plz help...

I have an asp.net login control on my website;

my users are stored in a SQL DB;

The problem is when I back-up my DB from my PC and restore it to my server; the logins doesn't work anymore. I mean the user I've created on my PC can't login on the website on the internet...

Thanks a lot...

I've had this same problem. If you delete the user from the database and re-add it, it will work. Does anyone know why this happens? Is there a numeric ID the server uses for the user name?|||

Thank god someone else had the same problem; it's been so long that I'm working on it that I allways thought it only happens to me..

Yes if I delete and then re-register same user it works but I can't say to my users please re-register:))

is there any way to programmaticaly do it? or any REAL solution??

How did you handle that? I mean does it work now?

|||Wait, when you say they are stored in a sql db, do you mean they are stored in a table or they are sql logins of the database? This happened to me when I had a user with a sql login.|||Backup/Restore does not transfer the logins. You need to generate the script to create the logins and run it to set the permissions after your restore.|||

They are in a table..

I'm not talking about SQL logins. Logins are aspnetdb logins for a web site. I mean users inside the aspnetdb_Membership and aspnetdb_Users tables...

|||The users may be in a table. You still need to run a script to SET the permissions for the users.|||

What script s that?

Users (I mean the user connecting by internet) already got permissions and re-mapped?

|||

The user exists in your table but he/she does not yet have the permissions to execute any scripts.

Check out BOL for sp_addlogin, sp_grantlogin, sp_defaultdb, etc.. If you are not sure of the scripts, try generating the script for existing logins.

|||

Hi,

I think the answer is hidden somewhere over there I didn't even realised that there could be that much stored procedure and functions... Anyways I found that sp's but I didn't understand anything:) how can I work em out from a query? Also I found "dt_validateloginparams" could that also be related?

Actually theres a parameter HasAccess like below:

EXEC

%%LocalLogin(Name= @.loginame).SetAccess(HasAccess= 1)

I think this is the key line but where does it store this HasAccess info? I couldn't find it in the tables?

Thanks a lot...

|||

In Enterprise Manager, right click on the DB -> All Tasks -> Generate Scripts -> Options tab -> select "Script SQL Server Logins" -> Back to General tab -> Click on Preview.

You can either paste the code into a stored proc and call it from your application or parameterize the scripts and use it for any login...as creative as you can get...

|||

Hi;

I'm sorry; I'm as lame as I couldn't figure it out. Maybe you already told me the answer but I don't know...

Could you please tell me what to do:)

Thanks a lot..

|||I just did. Thats the best I can do.|||Ok man; thanks.. I'll try..|||http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Logins some help plz

I have database called 'sco' it has the following users
+ sco_admin
+ sco_publish
+ sco_default
+ sco_config

I had to rebuild the machine this database was running on, and I have attached the db back now I need to make the logins again, however when i try and make a SQL login 'sco_admin' and give it access to 'sco' it says "user 'sco_admin' already exists". Which of course it does. I don't want to delete all the users and reset all the permissions.

So how do I associate SQL Login 'sco_admin' with DB User 'sco_admin'?

I had a look at the help but couldn't make much sense of what I was reading.

Also is there a way to keep the SQL Logins intact with the database??

Thanks In Advance,

Nicholas OrrSolved you have to use

sp_change_users_login

Logins and users question

Ok, let me preface my question with a little info about the application I am working on. I am in the middle of creating a project in VB.Net for my company. We have a data management system for handling sampling data. Now the database design is like so. The application is able to handle multiple "Sites". We create a new database for each site that is create dint eh application. These databases are identical in structure but the data is obviously different.

Now we don't actually delete any records in this application rather we mark items as deleted instead. This allows us to easily undo any changes that have been made to the data. When a change or delete are made, we record this change so that reports can be printed to show what changes were made and by who. This is all well and good, but my thoughts are this.

At the moment I am writing lots of VB code to handle adding these records of change and inserting them into the database... What I would like to be able to do is to just create Triggers on the tables to add them. This is something that I know how to do and i feel like it would be the better way to do it except for one thing... here comes the issue...

I have no way of knowing what user is logged in to my application from within the trigger because the application uses a single login for accessing the database. My thoughts are this... Would I be crazy to think that it would be a good idea to create SQL server logins for each user that is created in the application, giving these users access to only the database that they have been created in? This would allow me to determine who was logged in when the change was made and could then implement recording changes through triggers... I am not a very experienced dba programming is more my speciality. I know how to implement this idea, but I am just wondering if this sort of thing is considered bad practice or if it is something I should consider implementing...

Sorry for the novel there and thanks for any help or insight.

BrianNot sure if this helps but there is the suser_sname() which should return the login name (I usually put this in as the default value for my EnteredBy field and I'm not sure if it will work in your case with the single login but it may be worth investigating.)|||Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.|||Well, that is what I am looking for, but I was wondering if it makes sense to create a SQL server login for each user in my application.

It would seem so if you want to track who is inserting or modifying your data!|||We use connection pooling wih a single login to the database

The users have application security, and they log into the app via a user table, with passwrod verification done by an API to RACF

Every database call is limited to Stored Procedures

The Calling App must pass as a parameter to the sproc, the application user that is logged

Any modification that is done records the app user on the row

A trigger then moves the before image row to history as is

The last person making the modification is on the current row

logins and users

Hello,

How can I login in one databse from Sql Management Studio? Do I need one login for each user?

How does it work?

thanks,

Using the management studio you should be able to log on with a trusted connection. You will need to get the Database owner or admin to assign the permissions to each user that will need to connect... or you could create a network group in the AD and assign permissions to the user group in the databases that the users need to connect to.

|||yes, but how can I assign permission to this users. I mean trusted connection|||

Hi,

no you don′t need to. Trusted Connections / Windows Authentication is based on Windows security. Therefore you can also create a principal using a Windows group of your Windows domain or local server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Logins and users

Hi,
I would like to ask what is the difference between "Login" and "User" entity.
I know that a Login is a principal and I can use it for accessing db
resource, and I can to limit its rights. Why there is a "User" entity also?
Thanks,
Lubomir
At a very basic level, a login is at the server level and
allows connecting to the server.
A user is at the database level and allows access to the
database.
-Sue
On Tue, 7 Aug 2007 11:40:01 -0700, Lubomir
<Lubomir@.discussions.microsoft.com> wrote:

>Hi,
>I would like to ask what is the difference between "Login" and "User" entity.
>I know that a Login is a principal and I can use it for accessing db
>resource, and I can to limit its rights. Why there is a "User" entity also?
>Thanks,
>Lubomir
|||Thanks.
"Sue Hoegemeier" wrote:

> At a very basic level, a login is at the server level and
> allows connecting to the server.
> A user is at the database level and allows access to the
> database.
> -Sue
> On Tue, 7 Aug 2007 11:40:01 -0700, Lubomir
> <Lubomir@.discussions.microsoft.com> wrote:
>
>

Logins and users

Hi,
I would like to ask what is the difference between "Login" and "User" entity
.
I know that a Login is a principal and I can use it for accessing db
resource, and I can to limit its rights. Why there is a "User" entity also?
Thanks,
LubomirHi Lubomir,
Login is a principal that gains access to the server (to SQL Server
instance). It is generally used for granting permissions on server level
(configuration, database creation and so forth).
User is a principal created in specific database by mapping some single
login to it and is generally used for granting permissions in the database.
Read more in Books Online (SQL Server documentation, also available online).
You should also read about roles (server roles and database roles). Roles
are used for managing permissions in more effective way.
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uytkownik "Lubomir" <Lubomir@.discussions.microsoft.com> napisa w
wiadomoci news:29BC9649-D34E-4FAE-9855-E8E793AE84E0@.microsoft.com...
> Hi,
> I would like to ask what is the difference between "Login" and "User"
> entity.
> I know that a Login is a principal and I can use it for accessing db
> resource, and I can to limit its rights. Why there is a "User" entity
> also?
> Thanks,
> Lubomir|||At a very basic level, a login is at the server level and
allows connecting to the server.
A user is at the database level and allows access to the
database.
-Sue
On Tue, 7 Aug 2007 11:40:01 -0700, Lubomir
<Lubomir@.discussions.microsoft.com> wrote:

>Hi,
>I would like to ask what is the difference between "Login" and "User" entit
y.
>I know that a Login is a principal and I can use it for accessing db
>resource, and I can to limit its rights. Why there is a "User" entity also
?
>Thanks,
>Lubomir|||Thanks.
"Sue Hoegemeier" wrote:

> At a very basic level, a login is at the server level and
> allows connecting to the server.
> A user is at the database level and allows access to the
> database.
> -Sue
> On Tue, 7 Aug 2007 11:40:01 -0700, Lubomir
> <Lubomir@.discussions.microsoft.com> wrote:
>
>

Logins and users

Hi,
I would like to ask what is the difference between "Login" and "User" entity.
I know that a Login is a principal and I can use it for accessing db
resource, and I can to limit its rights. Why there is a "User" entity also?
Thanks,
LubomirHi Lubomir,
Login is a principal that gains access to the server (to SQL Server
instance). It is generally used for granting permissions on server level
(configuration, database creation and so forth).
User is a principal created in specific database by mapping some single
login to it and is generally used for granting permissions in the database.
Read more in Books Online (SQL Server documentation, also available online).
You should also read about roles (server roles and database roles). Roles
are used for managing permissions in more effective way.
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
U¿ytkownik "Lubomir" <Lubomir@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:29BC9649-D34E-4FAE-9855-E8E793AE84E0@.microsoft.com...
> Hi,
> I would like to ask what is the difference between "Login" and "User"
> entity.
> I know that a Login is a principal and I can use it for accessing db
> resource, and I can to limit its rights. Why there is a "User" entity
> also?
> Thanks,
> Lubomir|||At a very basic level, a login is at the server level and
allows connecting to the server.
A user is at the database level and allows access to the
database.
-Sue
On Tue, 7 Aug 2007 11:40:01 -0700, Lubomir
<Lubomir@.discussions.microsoft.com> wrote:
>Hi,
>I would like to ask what is the difference between "Login" and "User" entity.
>I know that a Login is a principal and I can use it for accessing db
>resource, and I can to limit its rights. Why there is a "User" entity also?
>Thanks,
>Lubomir|||Thanks.
"Sue Hoegemeier" wrote:
> At a very basic level, a login is at the server level and
> allows connecting to the server.
> A user is at the database level and allows access to the
> database.
> -Sue
> On Tue, 7 Aug 2007 11:40:01 -0700, Lubomir
> <Lubomir@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I would like to ask what is the difference between "Login" and "User" entity.
> >
> >I know that a Login is a principal and I can use it for accessing db
> >resource, and I can to limit its rights. Why there is a "User" entity also?
> >
> >Thanks,
> >
> >Lubomir
>

Logins and sp permissions

I am using NT logins and have Users defined in Security section of
Enterprise Manager and also in the database/users section, as db_Owners.
I can only get users to access the .ade file successfully from certain PCs.
The error is related to permissions on particular stored procedures that are
used to poulate the inital screen. The stored procedures populate list boxes
and appear blank when opened. However I have buttons associated with the
list loxes that sort the data via each column in the list box, and when
these are used the data appears in the list boxes.
On the PCs that fail the stored procedures are suffixed by ;1
I feel that it maybe PC specific but the workstations involved are similar
using a standard desktop configuration, latest service packs etc
TIA
WarwayTry using SQL Profiler to capture the traffic between a working machine and
a client that is failing.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Friday, March 9, 2012

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.
Nevermind. I answered my own q.
"CLM" wrote:

> I have always worked out of Enterprise Manager to view the permissions/users,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a lot
> of databases and I've found Ent Mgr is just too slow.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.Nevermind. I answered my own q.
"CLM" wrote:
> I have always worked out of Enterprise Manager to view the permissions/users,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a lot
> of databases and I've found Ent Mgr is just too slow.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users
,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.Nevermind. I answered my own q.
"CLM" wrote:

> I have always worked out of Enterprise Manager to view the permissions/use
rs,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I wa
nt
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a l
ot
> of databases and I've found Ent Mgr is just too slow.

logins after a restore

Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR
Check out sp_change_users_login in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"ChrisR" <noemail@.bla.com> wrote in message
news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR
|||Thanks Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eUA2UajOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Check out sp_change_users_login in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
> Ive always dropped/ recreated the SQL Users after restoring a backup from
> prodiuction to dev in order to make the Users usable after this scenario
> occurs. Is there a better way?
> TIA, ChrisR
>

logins after a restore

Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisRCheck out sp_change_users_login in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR|||Thanks Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eUA2UajOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Check out sp_change_users_login in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
> Ive always dropped/ recreated the SQL Users after restoring a backup from
> prodiuction to dev in order to make the Users usable after this scenario
> occurs. Is there a better way?
> TIA, ChrisR
>

Friday, March 30, 2012

long running transactions w/ other users needing to read data

Friday, March 23, 2012

logshipping load failure

Wednesday, March 21, 2012

Logon window - Another one

Monday, March 19, 2012

Logon Failed and Subscriptions

Monday, March 12, 2012

Logins, Users, Roles, Schemas

Logins to the website doesnt work after DB backing up and restoring

Logins some help plz

Logins and users question

logins and users

Logins and users

Logins and users

Logins and users

Logins and sp permissions

Friday, March 9, 2012

Logins and Permissions

Logins and Permissions

Logins and Permissions

logins after a restore

logins after a restore

sql login permissions

Blog Archive

About Me