Analog Of Xp_cmdshell With Limited Rights
Dec 15, 2004
Hi, we have a generic way to call DTS
CREATE procedure dbo.run_our_dts @Our_dts_namevarchar(8000)
as
declare@retcodeint
declare @run_sqlvarchar(8000)
select @run_sql = 'dtsrun /S '+@@servername+' /E /N '+@Our_dts_name
print @run_sql
exec @retcode=master..xp_cmdshell @run_sql
if @retcode<>0
begin
Raiserror('DTS RUN ERROR',16,1)
end
it works fine but ,Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps
we don't want to disable this setting
Does anyone now extended procedure
similar to xp_cmdshell, with abilty to
start dtsrun ?
Thank you
Alex
View 1 Replies
ADVERTISEMENT
Nov 16, 2005
Hi All,
How can I give the user view rights only?
View 4 Replies
View Related
Apr 17, 2007
Hi
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!
Cheers
View 9 Replies
View Related
Sep 11, 2006
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.)
View 3 Replies
View Related
Mar 14, 2004
I was bored, so I tried it.
It's not perfect (The results panes fonts mess with it a bit).
Best viewed if your QA's font is set to one of the Courier fonts and the results are viewed as text rather than grid.
I still want to get it right without using any cursors at all.
I will attempt this later.
Maybe someone else can improve on this though.
Here it is:
--**********************************************************
CREATE PROCEDURE AnalogTime AS
SET NOCOUNT ON
CREATE TABLE #ClockTimeDeg(Radius INT, ClockTimeHRS DECIMAL(18, 3), ClockTimeMin INT, Deg DECIMAL(18, 8), XCoOrd DECIMAL(18, 8), YCoOrd DECIMAL(18, 8), DISPLAY VARCHAR(2));
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
CREATE TABLE #Results(RowNo INT, AnalogClock VARCHAR(1000))
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
SELECT AnalogClock
FROM #Results
ORDER BY RowNo
DROP TABLE #ClockTimeDeg
DROP TABLE #Results
GO
exec analogtime
--**************************************************
Any suggestions and improvements welcome.
Duane.
View 19 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
Sep 22, 2006
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?
View 1 Replies
View Related
Feb 19, 2007
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! :-)
View 3 Replies
View Related
Feb 19, 2001
Dear all,
When I try to add the columns to 300,
But still can not over 300 column.
What's limited columns that MS SQL 7.0?
Thank you very much..
Gemmy.
View 2 Replies
View Related
Aug 29, 2005
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
View 2 Replies
View Related
Sep 25, 2007
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?
Thanks for your help,
Alistair.
View 1 Replies
View Related
May 21, 2007
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.
Hope I'm clear.
Thanks for any help!
View 4 Replies
View Related
Jan 21, 2008
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?
Thanks.
DanYeung
View 7 Replies
View Related
Nov 28, 2007
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
View 2 Replies
View Related
Jan 21, 2008
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?
Thanks.
DanYeung
View 3 Replies
View Related
Mar 28, 2007
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.
View 10 Replies
View Related
May 15, 2007
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.
View 3 Replies
View Related
Dec 6, 2007
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
View 1 Replies
View Related
Jul 8, 2004
I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.
Thanks.
View 2 Replies
View Related
Sep 3, 2002
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?
View 1 Replies
View Related
Sep 3, 2002
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?
View 1 Replies
View Related
Feb 15, 2008
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.
elsasoft.org
View 20 Replies
View Related
Jul 23, 2005
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.)
View 3 Replies
View Related
Sep 19, 2006
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.
View 2 Replies
View Related
May 5, 2006
Using System.Data.SqlClient is there a limit to the number of connections an application can have to a SQLMobile dB?
If
you have a look at the url below, it says "A device can only have a
small number of connections to an instance of SQL Server at any time"
Does this mean 2, or 8 or what?
Should a app try to use only 1 connection throughout, or can we get away with 2-3?
Is this the same on WM5.0/PPP2003?
Any advice in this area much appreciated!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_sqlclient_705.asp
p.s. I'm not interested in connection pooling!
View 1 Replies
View Related
Oct 14, 2006
hi all,
I installed sql server developer edition on
xp box. created a new ssis project and
then when i right click on control flow
then click new connections
i see only limited connection available.
how can i install the other connection option
such as oledb. etc.
thanks
joey
View 1 Replies
View Related
Apr 26, 2007
Hi all,
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?
Thanks!
View 1 Replies
View Related
Mar 20, 2007
Hello all,
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?
Thanks,
Rob
View 1 Replies
View Related
Mar 20, 2007
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?
Thanks,
Rob
View 11 Replies
View Related
Sep 7, 2006
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
View 3 Replies
View Related
Jul 28, 2005
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.
View 2 Replies
View Related
Apr 7, 2006
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???
View 1 Replies
View Related
Oct 16, 2006
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""
View 2 Replies
View Related
