Problem With Roles And Rights (solved)
Jan 8, 2008
Hello everybody,
I have a database, where all tables and stored procedures look like this:
dbo.table
dbo.sp
Is this naming because of the db owner creating the tables and stored procedures?
The db runs correctly in my asp.net aplication when i use a user that is db owner, but i want it to run with a 'normal' user that is in public role and has all rights except db_owner...
How can i solve my problem!
Using the user without db_owner rights brings in my application the error:
The EXECUTE permission was denied on the object 'myStoredProcedure', database 'myDatabase', schema 'dbo'.
Thanks for help!!!!
Steven****
--------------------------------------
Solution:
I have to give the User without DB_Owner Rights the schema permissions for dbo objects! This only works in sql server 2005!
View 1 Replies
ADVERTISEMENT
Jun 20, 2001
New to SQL so please be patient. I'd like to give my developers "See All/Do Nothing" rights to my production server. I want them be able to see everyting that I do, as SA, but not be able to change anything. Is this possible?
Thanks for the help...Art
View 2 Replies
View Related
Feb 20, 2006
Hi, SQL experts.
I'm
new in the SQL Server Express Configuring Stuff and I've a problem or
more exactly I don't have any idea about configuring an secure and
hacking save user for a SQL Express DB.
What server roles/server
permissions/endpoint permissions/database permissions are neccessary to
have an rw access SQLE user? Any articles available? Any suggestions?
Scenario is a Personal Webpage for my Family & Friends located in my house
- ASP.Net Starter Website: Personal Website
- 2 MDF DB's: ASPNETDB.MDF & PERSONAL.MDF (included with the Starter Website)
- Development on DEV PC; Deploy on SERVER PC
HW:
- SERVER PC with Win2k (fully patched)
- DEV PC with Win XP Sp2 (fully patched)
- LAN 100Mbit w NAT FW
- WAN Cable Modem 128/1500
SW:
- APACHE 2.0.55 with latest aspnet_mod
- ASP.NET 2.0
- SQL Server Express
- SQL Manager 2005 Lite
- MS Web Developer Express
What I've done so far:
- APACHE: configured & working
- aspnet_mode: configured & working
- SQLExpress: configured & working
- SQLBrowser: configured & working
- TCP/IP Protocol for SQLE: configured & working
- Mixed authentication for SQLE: configured & working
- ASPNETDB.MDF: attached, configured & working
- PERSONAL.MDF: attached, configured & working
- SQL Manager 2005 Lite: configured & working
- SQLE users: created one for ASPNETDB and one for PERSONAL
(with SQL Manager Lite; Sorry MS!!! your SQL Server Mgmt Studio Express sucks!)
- WEBSITE: changed connection string from local to remote
(local doesn't work, because the LocalSystem User on Win2K, which is
used by the SQLServer, has no User Profile. Also you cannot change the
Service User, so it won't work with the local connection string, but
with an attached and remote accessible DB it works just fine)
- WEBSITE: runs locally and remotely on DEV PC with remote DB's and custom users.
But
I'm concern about the two users I've created, to access the two DB's.
I've no idea, what rights/roles aso they need to use the DB for remote
(for development) and local (for production) access and to be secure?
Thanks for any help, ideas and suggestions.
Alex
View 4 Replies
View Related
Feb 27, 2004
How can I generate a list/view of all users/groups with appropriate roles and rigths?
View 3 Replies
View Related
Jul 12, 2007
The DBA at our location is demanding local admin (windows) right's to the box so he can function. Right now when he logs in i have given him right's to the inetpub directory, sql directory, i have set him as a sysadmin on sql2005 and gone into the http:\localhost
eports and set him up as a system manager and under site priveledges set him as a sys admin. When he tries to login and configure the report server he gets the following error:
Title-Reporting services configuration manager
Error-There was an error refreshing the UI. bla bla bla
A WMI error has occurred and no additional error information is availiable
Title-Reporting services configuration manager
Error-There was an error while switching panels. The most likely cause is an error retrieving WMI properties. bla bla bla
A WMI error has occurred and no additional error information is availiable
then when he's in sql server 2005 surface area configuation
Title-Surface Area Configuration
Error-Access denied (system.management)
Is there any documentation or anythign anyone can tell me that i can do to give this DBA full access to configure and admin the SQL portion of his system without giving him admin rights to the OS???
Please help!!
Thanks for any time anyone has taken to review this thread!!
View 8 Replies
View Related
Mar 5, 2006
Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
View 4 Replies
View Related
Jan 28, 2004
Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?
I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.
After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?
View 3 Replies
View Related
Jun 16, 2006
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
Many thanks,
Mark
View 1 Replies
View Related
May 6, 2007
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
View 3 Replies
View Related
Aug 24, 2006
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
View 14 Replies
View Related
May 3, 2005
Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest?
Thanks a lot.
View 4 Replies
View Related
Mar 2, 2006
I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
Thank you,
nu_dba
View 1 Replies
View Related
Apr 15, 2007
Hi,
I'm looking for some guidance/help regarding setting up a sa - lite account in SQL 2005. I need to give another admin rights to create/monitor maintenance plans, backup and restore databases, monitor performance/logins, but NOT be able to have any rights on several tables (and of course not being able to set user permissions).
I've tried using server and db roles but haven't been able to determine how to give someone w/o full sa rights access to maintenance plans.
If you can think of soemthing, please let m eknow.
Jenn
View 4 Replies
View Related
Mar 10, 2004
I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.
When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.
Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.
View 1 Replies
View Related
Sep 20, 2006
Hi,
Now it´s working fine!!!!
On the Firewall of the Server, I´ve added the default port 1433 on the exceptions.
thanx!!!!
View 1 Replies
View Related
Feb 21, 2008
The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.
Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.
View 3 Replies
View Related
Jun 24, 2006
Upper case sentece in normal case sentence
View 6 Replies
View Related
Feb 25, 2003
I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!
Function Main()
DTSDestination("a")=DTSSource("a")
DTSDestination("b")=DTSSource("b").Value + 100
Main = DTSTransformStat_OK
End Function
or
Function Main()
dim x
DTSDestination("a")=DTSSource("a")
x = DTSSource("b").Value + 100
DTSDestination("b").Value = x
Main = DTSTransformStat_OK
End Function
View 2 Replies
View Related
Dec 8, 1999
Hello friends I need a very ergent help!!!!!!!!!!!!!
Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on.
but the database is still running and users are accessing it.
This SQL database is accessed by an application(it can also create users on
the backend). I don't know how it happend ( i cant even get to errorlog).
I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.
Please help me on this matter, because I have to do lots of important tasks on this server.
I appreciate all of your help.
Thanks
Ragul
View 2 Replies
View Related
Mar 12, 2008
Hi, hope someone can help. I have two tables
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total
from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
View 4 Replies
View Related
Mar 23, 2008
Hello,uery - I'm Stuck
I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.
I'm attempting to count the number of times each m_id is returned after running this query:
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
which returns:
m_id
33
34
34
35
35
35
36
I want to count the number of times that m_id is returned so that the results of my query will be:
m_id | count
33 | 1
34 | 2
35 | 3
36 | 1
In my attempt to do this, I run the following query:
SELECT m_id, count( m_id ) AS "count"
FROM taglink
WHERE m_id
IN (
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
)
GROUP BY m_id
The problem I'm having is that the query returns:
m_id | count
33 | 3
34 | 2
35 | 3
36 | 3
It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."
Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!
View 4 Replies
View Related
Aug 9, 2006
i try to do a simple insert using SQLDataSource and a Stored Procedure
I try all night and always have this error
Object Must Implement IConvertible
I can’t understand where I wrong..
Looking around I understand is maybe a typecasting problema but I not find nothing more understeable..
Could someone tell me why Microsoft release this SQLdatasorce making more difficult a simple insert?
If someone could help me to fix this problem please
Here is the code
------------------------------------------------------------------------------------------------------------
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
SqlDataSource1.Insert()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
SqlDataSource1.Dispose()
End Try
End Sub
------------------------------------------------------------------------------------------------------------
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
InsertCommandType="StoredProcedure" InsertCommand="dbo.StoredProcedure1">
<InsertParameters>
<asp:ControlParameter ControlID="DropDownListCategory" Name="@CategoryID" PropertyName="SelectedValue"
Type="Int32" />
<asp:ControlParameter ControlID="DropDownListCategory" Name="@ModelNumber" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="txtModelName" Name="@ModelName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtProductImage" Name="@ProductImage" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtUnitCost" Name="@UnitCost" PropertyName="Text"
Type="Decimal" />
<asp:ControlParameter ControlID="txtDescription" Name="@Description" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtWeight" Name="@Weight" PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="txtDiscount" Name="@Discount" PropertyName="Text"
Type="Decimal" />
<asp:ControlParameter ControlID="chkIsActive" Name="@isActive" PropertyName="Checked"
Type="Boolean" />
<asp:ControlParameter ControlID="DropDownListAuthors" Name="@IDAuthor" PropertyName="SelectedItem"
Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.StoredProcedure1
@CategoryID int,
@ModelNumber nvarchar(50),
@ModelName nvarchar(50),
@ProductImage nvarchar(50),
@UnitCost money,
@Description varchar(500),
@weight money,
@IsActive bit,
@DiscountPerCent int,
@IDAuthor int
AS
INSERT INTO dbo.CMRC_Products (
CategoryID,
ModelNumber,
ModelName,
ProductImage,
UnitCost,
Description,
weight,
IsActive,
DiscountPerCent,
IDAuthor
)
VALUES (
@CategoryID,
@ModelNumber,
@ModelName,
@ProductImage,
@UnitCost,
@Description,
@weight,
@IsActive,
@DiscountPerCent,
@IDAuthor
)
SELECT @@IDENTITY as [NewID]
------------
View 4 Replies
View Related
Aug 9, 1999
i have a table "employee" , i have given grant all rights to a user
in sql 6.5 server . still whenever he tries to access the table from VB he gets an error "execute permission denied on table employee, owner dbo"
. if i log in the server with this user name and his password i am able to interact with the table . please help.
View 1 Replies
View Related
May 11, 2006
We've tried attaching the mdf files given to us by some customers using the script they've provided, too.
I created the database with the corresponding names, and tried to run their script. However, I get the message "The media family on device 'D:DatabaseDVVAD01.mdf' is incorrectly formed. SQL Server cannot process this media family."
What could possibly have gone wrong? Is it also possible that the file was not properly detached, or what?
View 1 Replies
View Related
May 14, 2008
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Please help!
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
FROM@Stage AS s1
select * from @Stage
My desired Results are
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300
But I am geting
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928
Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.
Please help!
View 15 Replies
View Related
May 28, 2008
First, I'm not sure what format to post the SQL query in, but I'm using it in the Pawn programming language, so it's all on one line. Sorry if it's hard to read :(
Ok, I am supplying you guys with my database that I am having problems with. Basically, I'm telling it to ORDER BY ASC but it's not ordering it correctly. It's almost correct, but not perfect. I'm not sure if SQLite is much different than SQL standard, that's why I differentiate.
Here is the SQLite database:
*deleted*
The problem comes in when you execute this query:
SELECT p.alias, min(s.fin_time), s.cps, s.gcs, s.boosts, s.wpns, s.score, s.score_id FROM climb_scores s JOIN climb_players p ON s.user_id = p.user_id JOIN (SELECT user_id, fin_time, score_id FROM climb_scores WHERE map_name="j2s_4floors" AND cps = 0) g ON g.score_id=s.score_id GROUP BY s.user_id ORDER BY s.fin_time ASC LIMIT 20
The end goal is to have fin_time sorted in ascending order, but it's not working right. Can someone clue me in to the problem?
thanks!
View 1 Replies
View Related
May 30, 2008
hi guys, im having a sql problem for reporting here. here is the table
CREATE TABLE `tblasset` (
`AssetID` int(11) NOT NULL,
`AssetName` varchar(50) NOT NULL,
PRIMARY KEY (`AssetID`)
);
INSERT INTO `tblasset` (`AssetID`, `AssetName`) VALUES
(1, 'IPOD'),
(2, 'Laptop'),
(3, 'Sony PS3'),
(4, 'Wooden Cupboard'),
(5, 'Fridge');
CREATE TABLE `tblassetplacement` (
`PlacementID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
`AssetID` int(11) NOT NULL,
`PlacementDate` bigint(20) NOT NULL,
`OfficerInChargeID` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PlacementID`)
);
INSERT INTO `tblassetplacement` (`PlacementID`, `LocationID`, `AssetID`, `PlacementDate`, `OfficerInChargeID`) VALUES
(1, 3, 1, 1209628156, 'John'),
(2, 6, 1, 1209800956, 'Susan'),
(3, 3, 3, 1209714556, 'Erik'),
(4, 4, 3, 1210405756, 'Albert'),
(5, 5, 3, 1211096956, 'Fred');
CREATE TABLE `tbllocation` (
`LocationID` int(11) NOT NULL,
`LocationName` varchar(50) NOT NULL,
`ParentID` int(11) DEFAULT NULL,
PRIMARY KEY (`LocationID`),
KEY `ParentID` (`ParentID`)
);
INSERT INTO `tbllocation` (`LocationID`, `LocationName`, `ParentID`) VALUES
(1, 'Building A', NULL),
(2, 'Building B', NULL),
(3, 'Room 1', 1),
(4, 'Room 2', 1),
(5, 'Partition 1', 4),
(6, 'Room 1', 2);
basically the location looks like this
Location Tree View
---
.___ Building A
. .___ Room 1
. .___ Room 2
. .___ Partition 1
.
.___ Building B
. .___ Room 1
on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint.
the higher of its value means it is the latest placement.
so i had created a sql to pull the data like this:
SELECT
tblasset.AssetName,
tblassetplacement.OfficerInChargeID,
tblassetplacement.Locationid,
tbllocation.LocationName
FROM
tblasset,tblassetplacement, tbllocation,
(select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl
where
tblasset.AssetID = tblassetplacement.AssetID
and tblassetplacement.PlacementDate = temptbl.LatestDate
and tblassetplacement.AssetID = temptbl.AssetID
and tbllocation.LocationID=tblassetplacement.LocationID
;
this will output:
---
AssetNameOfficerInChargeIDLocationIDLocationName
IPODSusan6Room 1
Sony PS3Fred5Partition 1
i would need to have a column ParentLocationName that directly shows the name of the parent's location as below:
---
AssetNameOfficerInChargeIDLocationIDLocationNameParentLocationName
IPODSusan6Room 1Building B
Sony PS3Fred5Partition 1Building A
is there a way to pull the parent's location name in one sql syntax?
View 4 Replies
View Related
Sep 14, 2006
Is there a way to supress output on one column in a SP, using data from the same row?
Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO
----------------
-Stephen
View 4 Replies
View Related
Jan 8, 2007
Hi,
I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.
Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.
Why are all LVFs still active after detach/attach and checkpoint?
How can I shrink the LDF file?
Thanks.
Alec
View 1 Replies
View Related
Jul 23, 2005
Hello,We have an ADO.NET application using .NET version 1.1.4322 SP1. It iscalling stored procedures in a database that it never written to. Theonly thing the stored procedures do is a select statement on a fewtables that are joined. In the last few weeks we have experiencedissues where, two or three of these stored procedures consistentlytimeout. When we recompile the stored procedures, the problem goesaway for a period of time (anywhere between a few hours and a fewdays), then it reoccurs with the same two or three stored procedures.We have tried running the stored procedures with the same parametersagainst the same server using query analyzer with the same user andconnection settings while we are having the timeout issues, and theyperformed normally (in the 5 second range). We have also put thedatabase into "read only" mode, which has improved performanceduring normal times, but the issue continues to occur.Our trace data shows that during the timeouts periods, the storedprocedures perform the same number of reads and writes, and using thesame amount of CPU as during normal times, but the duration increasesfrom 5-6 seconds to 30 seconds (when the server receives the timeoutrequest).We have found no blocking on any of the tables (they are, after all,read only).Finally, we compared execution plans for the stored procedures when wehad timeouts to right after we recompile and alleviate the issue, andthe plans are identical.What could be causing this problem? Does recompiling a storedprocedure affect the Sql Server .Net Data provider?Thanks in advance
View 1 Replies
View Related
Sep 3, 2005
Sory for starting a new message, it won't let me reply to the original.Problem has been solved.osql -U sa -s localhostBALTDDoes the trick.Thanks for everyone who had a look at it anyway!Enjoy your weekend!
View 1 Replies
View Related
Mar 29, 2008
I have 15 websites running on my server, which also has SQL Express installed.
every other day my webpages whch connect by sql connection string, give an error.
Event Type: Failure Audit
Event Source: MSSQL$SQLEXPRESS
Event Category: (4)
Event ID: 18456
Date: 29/03/2008
Time: 18:20:07
User: N/A
Computer: DSVR006063
Description:
Login failed for user 'db'. [CLIENT: <local machine>]
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 16 00 00 00 44 00 53 00 ....D.S.
0010: 56 00 52 00 30 00 30 00 V.R.0.0.
0018: 36 00 30 00 36 00 33 00 6.0.6.3.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 07 00 00 00 6d 00 61 00 ....m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00
Restarting SQL Service does not resolve, however rebooting server, without making any changed sorts the problem.
does my sql account need access to the master?
Is this weird or am i missing something really obvious.
View 8 Replies
View Related
