Does anyone here know why xp_fixeddrives with a limited user returns an empty result set on 2005?
In essence I want to create a report which shows disk space remaining, an upgrade of an old one. It works fine on 2000, but seems to need sysadmin rights on 2k5. I would appreciate it if someone can suggest how to get it working - or an alternative solution!
I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?
I solved it by checking "Control server" under "Properties" > "Securables" for the login, but I don't actually want this login to have full control.
(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)
DECLARE @Mins INT DECLARE @Hrs DECIMAL(18, 3) DECLARE @Deg DECIMAL(18,8) DECLARE @XCoOrd INT DECLARE @YCoOrd INT DECLARE @CurXCoOrd INT DECLARE @CurYCoOrd INT DECLARE @PrevYCoOrd INT DECLARE @Radius DECIMAL(18, 8) DECLARE @InsString VARCHAR(1000) DECLARE @Display VARCHAR(2) DECLARE @CurrKey INT DECLARE @PrevKey INT DECLARE @CurrTime DATETIME DECLARE @HrsVal DECIMAL(18, 8) DECLARE @MinsVal INT
SET @CurrTime = GETDATE() SET @MinsVal = DATEPART(MINUTE, @CurrTime) SET @HrsVal = CAST(DATEPART(HOUR, @CurrTime) AS DECIMAL(18, 8)) + (CAST((@MinsVal / 12) AS DECIMAL(18, 2)) * 0.2)
IF @HrsVal >= 13 BEGIN SET @HrsVal = @HrsVal - 12 END
SET @Radius = 1 WHILE @Radius <= 24 BEGIN --******Store the x, y co-ords of the clocks hrs and minutes using trig functions --******Clock Radius = 24 --****** The length of the Y, coords is halved because it seems like the results panel provides more height to each character than width. SET @Deg = 90 SET @Hrs = 12 SET @Mins = 0 WHILE @Mins < 60 BEGIN INSERT INTO #ClockTimeDeg SELECT @Radius, @Hrs, @Mins, @Deg, CAST(24 + (@Radius * (CASE WHEN TAN(RADIANS(@Deg)) = 0 THEN -24 ELSE ((SIN(RADIANS(@Deg))) / TAN(RADIANS(@Deg))) END)) + 0.499999999 AS INT), (48 - CAST(24 + @Radius * (SIN(RADIANS(@Deg))) + 0.499999999 AS INT) / 2), CASE WHEN (@HrsVal = @Hrs AND @Radius < 11) OR (@MinsVal = @Mins AND @Radius < 21) THEN '*' ELSE CASE WHEN @Radius = 23 AND @Hrs = CAST(@Hrs as INT) THEN LTRIM(STR(@HRS, 2)) ELSE ' ' END END SET @Hrs = @Hrs + 0.2 IF @Hrs = 13 BEGIN SET @HRS = 1 END SET @Mins = @Mins + 1 IF @Deg > 353 BEGIN SET @Deg = 0 END ELSE BEGIN SET @Deg = @Deg + 6 END END SET @Radius = @Radius + 1 END
DECLARE DispCur CURSOR FOR SELECT CASE WHEN XCoOrd <= 0 THEN 48 ELSE XCoOrd END, YCoOrd, Display FROM #ClockTimeDeg WHERE Display<> ' ' ORDER BY 2, 1 DESC
OPEN DispCur FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display SET @PrevYCoOrd = 0 SET @PrevKey = 0 SET @XCoOrd = 1 SET @InsString = '' WHILE @@FETCH_STATUS = 0 BEGIN IF @CurYCoOrd <> @PrevYCoOrd BEGIN INSERT INTO #Results SELECT @CurYCoOrd, @InsString SET @InsString = '' SET @PrevYCoOrd = @CurYCoOrd SET @XCoOrd = 50 END WHILE @XCoOrd > @CurXCoOrd BEGIN SET @InsString = @InsString + ' ' SET @XCoOrd = @XCoOrd - 1 END SET @InsString = @InsString + @Display SET @XCoOrd = @XCoOrd - 1 FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display END
CLOSE DispCur DEALLOCATE DispCur
INSERT INTO #Results SELECT @CurYCoOrd, @InsString
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!!
I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?
I'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do? (In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.) Thanks in advance for any advice! :-)
hi all, I have a field which name is Information and it type is Varchar (8000),but some time data access than 8000 character, my client told me,make this field to store Unlimited data. So how can i achive this task, i m using VS 2003 (ASP.NET with VB.NET) with SQL 2000. Thanks Shally
Hiya - this might be a bit of a simple question but please bear with me! I have looked reasonably hard for this but can not find an answer:
I have an MSSQL 2000 server running on PC-A and would like to limit certain users (e.g. admin) to be only able to logon when using the actual PC-A machine.
I am aware that you can acheive this in MySQL with the "insert into user (host, user, password) values (localhost, username, password);" command. Is there an equivalent way to do this in MSSQL?
Hi, I couldn't make a clear title without writing 2 lines.
My problem (I tried to find out through the archives): this scenario will be used for several DBs on severals servers.
The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.
I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file. This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore. Process is too long and the time estimated to start next task (and the backup keep growing).
Is there a way to do everything via SQL2005 script (job)?
Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question). The best way is to use log shipping as well but sql2005 is needed on both sides.
I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?
I want to use TRIGGER_NESTLEVEL to test if a specific trigger is on the execution stack.
Normally, the table with the trigger is accessed by stored procedures and the user does not have any direct access to the table.
I am having problems doing this because I need to use OBJECT_ID to get the ID to pass to TRIGGER_NESTLEVEL. OBJECT_ID is unable to see the table when the stored procedure is run as that limited user account.
How can I use TRIGGER_NESTLEVEL in this scenario without granting this user permissions on the table?
Here is complete code to reproduce. The output is: Nest level is: 1 (running as admin)
Nest level is: (null) (running as limited user)
The output that I want would have a 1 instead of (null). Hard coding the specific OBJECT_ID is not the solution that I am looking for, since I want to use identical database scripts on separate installs of my database.
Code Block IF OBJECT_ID(N'Hello') IS NOT NULL DROP TABLE Hello IF DATABASE_PRINCIPAL_ID(N'TestUser') IS NOT NULL DROP USER TestUser IF OBJECT_ID(N'HelloProcedure') IS NOT NULL DROP PROCEDURE HelloProcedure GO
CREATE TABLE Hello ( ID INT PRIMARY KEY IDENTITY, Data INT NULL ) GO
CREATE TRIGGER HelloTrigger1 ON Hello FOR INSERT AS SET NOCOUNT ON UPDATE Hello SET Data = 1234 WHERE ID IN (SELECT ID FROM inserted) GO
CREATE TRIGGER HelloTrigger2 ON Hello FOR UPDATE AS SET NOCOUNT ON PRINT N'Nest level is: ' + ISNULL(CAST(TRIGGER_NESTLEVEL(OBJECT_ID(N'HelloTrigger1')) AS NVARCHAR(128)), N'(null)') GO
CREATE PROCEDURE HelloProcedure AS SET NOCOUNT ON INSERT INTO Hello VALUES (0) GO
CREATE USER TestUser WITHOUT LOGIN GO
GRANT EXECUTE ON HelloProcedure TO TestUser GO
EXEC (N'HelloProcedure') -- Prints: Nest level is: 1 GO
EXEC (N'HelloProcedure') AS USER = N'TestUser' -- Prints: Nest level is: (null) GO
I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?
I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?
I don't seem to find any property set to 8 though.
Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.
Hi all; qrymillcgetbytype_app ( stored query in MS-Access) in my code :
MS-ACCESS Part: query defination PARAMETERS prmMillToolType Long; SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length] FROM MILLC WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True)) ORDER BY MILLC.ID;
C++ PART bstrSQL contain stored query name "qrymillcgetbytype_app" hr = piRecordSet->put_Source(bstrSQL); piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown)
it opens fine but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 "
it displays the same output. Where clause won't work? Pls help me out.
Im using the Xquery:SELECT @xmlDoc.query(' for $item in (/Collection/Content) where $item/Html/root/DocInfo/Webinar = "White Paper" order by $item[1]/Html[1]/root[1]/DocInfo[1]/Title[1] ascending return $item '); I only want to return the TOP 5 nodes (not the entire nodelist). What is the FLOWR expression or simplest way to return the limited nodeset? Thanks
Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.
Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?
Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.
Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?
someone recently told me that their bcp export of a table with over MAX_INT rows failed because BCP hit an overflow in the internal counter that bcp uses to tell you how many rows it's exported. You know how it write "rows copied to host file: 40000" to stdout? That number got to MAX_INT, then went negative, then BCP crashed. So they had to start all over and break things up with the -F, -L flags.
I was rather surprised that a program that claims to handle "bulk" data would use a 32 bit int to accumulate the count. I suppose the original sybase dev figured nobody would ever want to export more than 2b rows.
just wondering, has anyone seen this before?
I am about to kick off a similar large export and am planning to break it up so as not to hit this, but that also means I won't be able to verify that it is indeed a problem.
I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)
I have a table with entries tied to a membership database. The problemis that I want to select a limit of sixteen entries per member, perday, where some members have 16+ entries per day.I have this so far ( which I've simplified for this post)SELECT dbo.members.firstname, dbo.members.lastname,dbo.entries.gameDayFROM dbo.members INNER JOINdbo.entries ON dbo.members.memberID =dbo.entries.memberIDIf it's day 5, each member should have 80 total.How can I change this to select only 16 entries for each member, makingsure it's 16 per day based on dbo.entries.gameDay?Thanks for your help.
I have a problem just today after added some tasks. I can only get the "OnPrevalidate" logging info only in both sysdts90 and a text file. Have I messed up with some setting?
I'm very new to this so excuse my naiveity...I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>sqlexpressuserid I can only see the systemdb's...I am trying to view adventure works, just to see how this remote connection stuff works...any ideas?
I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>sqlexpressuserid I can only see the system db's...I am trying to view adventure works, which I can see on the desktop fine using the same login...any ideas?
Hi I am using sql express to automatically generate users and logins from t-sql. I seem to be having a problem when it comes to restricting their access though. I only want them to be able to select on views and execute stored procedures. at the moment I have created a new database role, schema, login, and user, then added the user to the role but how do I restrict access to the above areas? do the restrictions go on the role, schema, or user? I must confess I find the whole schema and role issue a little confusing and just when I think I've got it, it turns out I haven't :( thanks
Hello all,I have a field defined as VARCHAR(8000) yet it only accepts a maximum of 1024 characters. Does anyone know how I can save 8000 characters in a single field?Thanks,Bill.
Hi, For the SQL Express & Std Edition have rows limited.... I have try convert a 3,667,345 rows data into SQL Sever can the SQL Expree 2005 and SQL Std 2000 support this volume of rows???
Ankush Jain writes "I m using Sql Server 2000 in Windoes XP (SP2) with Limited Window Account. I m new to Sql Server.Please You can tell me how to create database. When i expand the console root in Enterprise Manager it will show an error : "A connection could not be established to Local""
