We have Websense installed on one of our servers.
The database is MSSQL 2005, and it's configured using the local system account to run all of the services.
The sa account keeps locking repeatedly, even though I have manually unlocked it using SSMS. And it locks right away. I cannot log into the database using the sa account, but can see it through a Domain Admin account.
Hi,
I need to secure an sqlserver database such that it can only be accessed from an application and to prevent anyone with full admin rights on their local machine and an sqlserver licence from getting in to the database.
I am struggling with controlling access to the database from the sa account. If I attach to the database from a second instance of sqlserver which is different than that where the database was created then I am able to gain full access no problems, which is of course The Problem.
From what I can work out.
1. sa is dbo (and this cannot be changed)
2. dbo has the role of db_owner (and this cannot be changed)
3. the permissions for the db_owner role cannot be changed.
4. the password for sa is set at the level of sqlserver and not per database
.....so any sa can access any database.
I don't believe this so have to be missing something significant, any light on the subject would be gratefully received.
KeithT
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View RelatedI have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedI have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
- Larry
Hi everyone,
I have a question about SQL Server 2005. I have written an ASP.Net 2.0 Web Application and it is using SQL Server 2005 as Database.
In the last few days I noticed that the app is down sometimes. To analyze the problem I looked at the activity monitor in SQL Management Studio. I can see there approximately 170 processinfos.
I want to describe the column values of the process infos:
Process-ID: Unique ID and a red down-showing-arrow-icon
User: My UserDatabase: My DatabaseStatus: sleepingCommand: AWAITING COMMANDApplication: .Net SqlClient Data Provider
When I click Locks by Object, I can see the IDs of the Processinfos. Again I will show some colums:Type: DATABASERequirementtype: LOCKRequirementstate: GRANTOwnertype: SHARED_TRANSACTION_WORKSPACEDatabase: My Database
So my question is, does this mean, that i have locked the db? How are they handled? For example I have a windows service, which is doing checks in db every 10 seconds. I can see, that each check generates a new processinfo?
Is this usual, or am I doing something wrong?
Thnaks for help,Byeee
When I run a select statement : select 'X' from table1 where c1 = condition locking on indexes behaves as expected
However if I run select 1 from table1 where c1 = condition locking on indexes goes wild locking pages and rows on indexes that are not even referenced in the query. Any ideas Why?
Hello All,
I'm just migrating from oracle to SQL.Can anybody tell me that how effectively I can use Row level locking in SQL? If tow users are attemping to
Moify same record how i can deal it in Back end(SQL)?
Thanks in Advance.
Suchee
currently im using NT 4 (SP 6a), Intel Pentium III 600 (DUal processor) and 1GB RAM.
the problem that i facing now is DEADLOCK. the lock type in my database is 'DB type', how can i change to 'KEY' type or 'TAB' type???
pls help me and thanx in advance... :)
from comel
i have an application in production(sql 6.5 ) which causes locking which times out my other processes , iwant to capture time the locking takes place i have found in bol that i can get time deadlock occurs using trace flag 3605 in sql7.0 ,if i have to use trace flag is it ok with dbcc traceon or -T option in startup is recommended.
any advice would be appreciated
tia
ram
I have used DTS in the past to copy information in certain tables in production over the top of those same tables in test. However, the process is now failing. Does DTS require an exclusive lock on the source table, as well as the destination table during the export process? Will shared locks on the table I need to copy prevent DTS from completing the process?
View 4 Replies View RelatedHi
We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.
LE threshold maximum is set to 200.
LE threshold minimum is set to 20.
LE threshold percentage is set to 0.
Locks is set to 0.
I have also included the stored procedure, which we use to update the history table.
As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?
Any help will be greatly appreciated.
Winston
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000)
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000)
declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)
set nocount on
declare @sex char(1)
declare @huin integer
declare @huan integer
declare @hmailingdate char(8)
declare @mailtot integer
declare @mail12m integer
declare @lastday char(8)
open minihist
fetch next from minihist into @huin,@huan,@hmailingdate
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @mailtot = 1 select @mail12m = 0
/*** Get the gender ***/
select @sex = gender from name where uin = @huin
/*** Calculate if mailed in the last twelwe months ***/
if (@hmailingdate <> null) and (@hmailingdate > '19980524')
select @mail12m = @mail12m +1
/*** Get info for this uan from address_summary ***/
select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan
/*** Insert a row into address_summary if doesn't exist ***/
IF @@rowcount = 0
Insert into address_summary ( uan, uin,mailed_total,Mailed_12months, last_date,last_gender)
values (@huan,@huin,1, @mail12m, @hmailingdate,@sex)
ELSE
/*** compare recency ***/
if (@hmailingdate < @lastday) or (@hmailingdate is null) /* history record is older */
update address_summary
set mailed_total = @mailtot,
mailed_12months = @mail12m
where uan = @huan
else
update address_summary
set uin = @huin,
mailed_total = @mailtot,
mailed_12months = @mail12m,
last_date = @hmailingdate,
last_gender = @sex
where uan = @huan
end
fetch next from minihist into @huin,@huan,@hmailingdate
end
deallocate minihist
Hi,
We are running SQL 6.5 in Produciton and I'm getting one blocking problem but mostly I kill the process and whenever I check the SQL Error Log I see this message :
Error : 17824, Severity: 10, State: 0
Unable to write to ListenOn connection '1433', loginname 'XXXY', hostname 'DT SA'.
OS Error : 64, The specified network name is no longer available.
Error 1608, Severity: 21, State: 0
Hi,
I'm trying to use the pessimistic row locking of SQL to get following result.
When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.
I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.
How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?
For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.
I hope this is clear enough.
Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.
But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @@error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.
Tom
Hello .
I am using SQL Server 2000 in order to create a multi user program that accesses data.
The problem is that multiple users will update and select data at the same time at the same table.
Is there a way to avoid deadlocks ?
I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update.
and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.
does anybody know much about these ways? do you know where i can find code for this ?
is there a better way?
thanks !
and happy new year !
Hi all, firstly I would like to apologise because I don't actually use sql or know diddly squat about it. I am a network administrator and have a problem with a user's domain account getting locked out everytime he starts his sqlagent service (we are running a windows 2003 domain). I know this a vary vague post and I am sorry for that. I am just after some general ideas/information on why this keeps happening. Any help greatly appreciated.
Thanks
Rich.
deepak writes "how to lock the record while using a query
"select id,name from students"
i want to know various locks in sqlserver and and each of its use in insert ,update,delete and select etc. i am using it from visual basic 6.0
pls give reply fast"
Hi
On SQL i have created a query. Here is the code:
use DB1;
select * from [Jobs]
select resource_type, request_mode, request_status, request_session_id
from sys.dm_tran_locks
It produces the following results when run:
|resource_type | request_mode | request_status | request_session_id
|Database | S | Grant | 51
|Database | S | Grant | 54
What is "S"? what are the other possibilities and their meaning for this field.
And.. 51 and 54...what are they exactly? Are they individual people or user ids?
For example, could 51 be "Advanced users" and 54 be "Generic Users" under SQL security?
My next question is...
I suspect i have too many Indexes on my table "Jobs". I suspect it is causing page locks. Especially when someone is updating the records.
I will run this query when users complain to me about not being able to edit records.
Ok..Question is...if i have a PageLocking entrant..
Through SQL manager..is it possible to boot a user off temporarily..?
How do you do it?
Thanks
In Advance
Conor
Hi All, Please help me out how to implement the locking in below scenarioReq - There are two tables Table1 & Table2 If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated. Now the sync of table1 & table2 is working fine.My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.The other screen shouldnt allow updation in table2 and hence in table1.This is very common locking functionality ...but am not getting any way to implement it , Please advise.Srain.
View 1 Replies View RelatedHi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
I have a busy transactional table , I wanna use row level locking mechanism in msSQL.
SELECT * FROM PARTY WITH (UPDLOCK ROWLOCK)
where LastName ='Clinton'
is there any downsides of this approach?
I'm using Sql Server 2005...
I'm creating a transaction and enlisting the commands inside vb.net code as well as surrounding the t-sql in an "Begin Trans --- Commit Trans" block. I also have the Isolation level set to the highest (Serializable) in the vb.net code and the sprocs.
I'm running 4 instances of the app on 1 server and 4 instances of the app on another server. I am handling the lockouts just fine and writing them to an error table within the db. The app keeps spinning and producing data just fine.
There are 3 places where the locking may occur within the app. Two of them are just fine (which is a select and and insert). The app will eventually cycle around and pick up the records taht may have been locked out. My concern is the Update portion which updates stats based off the Insert done previously. If the records never get updated, the only way I would know if they were processed would be to check in our Error table to see if the record exists.
I would like to know if there is any way possible to cut down on the number of lockouts (which may be perfectly normal) and to get a way to update that table I just talked about. Should I be using different isolation levels, etc. --- anything of importance might be useful.
Hi all,
What I'm trying to do os this: have an application set a lock on a specific row in a table, so other applications can see it's busy.
So, I use "SELECT * FROM mytable WITH(ROWLOCK, HOLDLOCK) WHERE condition" to set the lock. That should lock a row until I close this recordset (me thinks anyway...)
Then to detect I use "SELECT * FROM sametable WITH(READPAST) WHERE samecondition". If the row I'm looking for is locked, this select will skip that row, so I get an empty selection.
That's what I want to happen anyway, but in the real world this doesn't seem to work. It doesn't lock, or it doesn't skip....
Any hints/tips welcome!
Thanx!
Duq
I have a table X with 61390 rows on it. IT is a crucial table which is being read and updated constantly. If I create a clustered index on the Primary Key (identity column) and run UPDATE x SET c1 = 1234 where PKCOL = 4321 the best lock I can get is a table Lock. If I create a compound non-clustered index on C1 and PKCOL the best lock I can get is a Page Lock. PAge Lock is OK but as this table is in High demand I want a row lock so others can carry on referenceing other parts of the table. WHy does it take a page lock and not a Row lock?
View 1 Replies View RelatedGurus,
I am trying to execute this stored procedure when I try to change all occurences of a field in a table.
(
@Dept char(8),
@DDept char(8)
)
As
Set NoCOUNt On
Begin
Select '@DDept'
update phodept set fo_dept = @Ddept
where fo_dept = @Dept
update phone set fo_dept = @Ddept where fo_dept = @Dept
End
GO
The table/database is being used by others, generally in a read only mode.
via a VB 5.0 FE program.
The Stored procedure, when it is invoked, just hangs like it is waiting for exclusive use of the table.
Is there a way around it, without doing major surgery on the VB code?
Thanks.
Sam
Hi
I've got table JOBS with JOB_ID, ORIGINATOR_ID etc. I prepare stored procedure that creates job, that mean,
inserts new record in table JOBS, and return JOB_ID as a result.
return (select MAX (JOB_ID) from JOBS where ORIGINATOR__ID = PARTICULAR_USER_ID)
The problem is when user is logged in two stations, runs two application and create job at the same time.
It is possible that both application receive the same MAX (JOB_ID)
Any suggestions how to lock records, or do it in a different way.
Michal
I have a stored-procedure which insert's records. In it I have a Begin Tran so if it fails I can run a rollback. When I'm inserting big number of records it creates X locks and it start's blocking other users.
View 3 Replies View RelatedHello,
I need help in record locking. As soon as user retrieve records I want to lock those records in database. When other user try to retrieve records from the same table i want to retrieve records those are not locked by other user. How do I do this? Please help...
Thanks in a millian.
Sarika
In Query Analyzer I have 2 connections active. In the first connection I have the following SQL Insert statement:
Begin Tran
Update Account
Set SomeField = 0
Where CustomerId >= 1000 And CustomerId <= 1101
-----------------
In the second connection I have the following SQL Insert statement
Begin Tran
Insert Into Account (Account,AccountName,AccountTypeRowId,CustomerId,U serId)
Values('MMM-989', 'Test Account', 1, 1098,207 )
-----------------
The query in the second connection will hang waiting for the query in the first connection to finish. The query in the first connection touches 767 records (out of a total of 9174).
If I change the Where statement in the first connection to "... And CustomerId <= 1100", the query in the second connection will execute immediately. The query in the first connection is now only touching 645 records instead of 767.
Could someone explain to me why this is happening?