Granting xp_cmdshell permission to SQL Login

Hi database geeks and MVPs!
Using SQL Server 2005 SP2
I have a stored procedure in my database which calls xp_cmdshell to run a
little task. I have done the following to allow this proc to be executed by
a
non-privileged user:
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
I get the following error:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
through via a stored procedure?
Thanks,
Mark.Mark
Did you restart MSSQLSERVICE after granting permissions?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Yes I did. No effect.
"Uri Dimant" wrote:

> Mark
> Did you restart MSSQLSERVICE after granting permissions?
>|||Hello Mark,
You can't use xp_cmdshell because you do not enable it. Because of the
'allow updates' option.
Your 'allow updates' server setting must be enabled. If you want to leave it
enabled then you'll need to run your code as the following to enable
xp_cmdshell
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
If you disable your 'allow updates' option using the following code
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
Then you'll be able to run your code as it is (without with override
thing...)
P.S.
I learned this solution from Jasper Smith, thanks to him.
Ekrem ?nsoy
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Mark
xp_cmdshell requires CONTROL SERVER permission.
Does the user have it?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
> Yes I did. No effect.
> "Uri Dimant" wrote:
>
>|||Hi Uri,
I have changed my script to incorporate that permission, but I still get the
same error. xp_cmdshell works fine when run as a sysadmin.
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
--EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
doesn't work either
GRANT CONTROL SERVER TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
go
/*
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
*/
"Uri Dimant" wrote:

> Mark
> xp_cmdshell requires CONTROL SERVER permission.
> Does the user have it?
>
>
>
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
>
>|||When I type "NET HELPMSG 1329" from the command prompt, I get message "Logon
failure: user not allowed to log on to this computer."
Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
has permissions to login locally.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:FF048394-0029-4E1B-931F-E6B9E2F9CFF3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I have changed my script to incorporate that permission, but I still get
> the
> same error. xp_cmdshell works fine when run as a sysadmin.
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> --EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
> doesn't work either
> GRANT CONTROL SERVER TO mysqllogin
>
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> go
> /*
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> */
>
> "Uri Dimant" wrote:
>|||Hi Dan,
That's the point. I cannot grant access to a SQL Login. It works fine with a
Windows login, but I have a SQL Login.
From BOL:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password'
} ]
Arguments
NULL
Specifies that the proxy credential should be deleted.
account_name
Specifies a Windows login that will be the proxy.
Mark.
"Dan Guzman" wrote:

> When I type "NET HELPMSG 1329" from the command prompt, I get message "Log
on
> failure: user not allowed to log on to this computer."
> Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
> has permissions to login locally.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
xp_cmdshell needs an OS security context when it runs. That security
context is the Windows xp_cmdshell proxy account when it's executed by a
non-sysadmin user. The 1329 error isn't related to the SQL login executing
xp_cmdshell but is rather the Windows error code returned because the
xp_cmdshell proxy account doesn't have the needed Windows permissions
My guess is that the Windows login you mentioned is a sysadmin role member.
Xp_cmdshell runs under the context of the SQL Server service account when
executed by a sysadmin role member and that account probably has different
permissions than the proxy account
I successfully ran a modified of your original script on my test system. My
xp_cmdshell proxy account is a minimally privileged domain user account and
. I didn't grant CONTROL SERVER permission..
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:61EA5CCA-4C65-410C-959B-7B67195F2DFD@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
> From BOL:
> sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'passwor
d' } ]
> Arguments
> NULL
> Specifies that the proxy credential should be deleted.
> account_name
> Specifies a Windows login that will be the proxy.
>
> Mark.
> "Dan Guzman" wrote:
>

Granting stored procedure execute permissions from ASP.NET?

Bit of an emergency!
I do not have direct access to our SQL Server but I have full FTP access to the web server and have the db Username/passwords.
I need to grant execute permissions on a stored procedure, can I do this from an asp/ASP.NET page?
The DB guys take 24 hours to run a script against the database!
Any help would be greatfully recieved.
Rich
You can use a SqlCommand object, and set the command text to something like this:
Grant Execute Onsp_name To ASPNET
Then use .ExecuteNonQuery()

Granting Permissions to a stored procedure

I have a stored procedure sp_LogError that it self executes the
xp_cmdShell procedure.
When I execute the procedure using a 'normal' acount with only public
acces to the database i get an error:
EXECUTE permission denied on object 'xp_cmdshell', Database 'master',
owner 'dbo'
sp_LogError is owned by dbo and all users have permission to access it.
It runs fine under the sa account.
What do I need to do so that all users can execute sp_LogError without
granting all users access to xp_cmdShell.
Thanks In Advance
MalachyHi,
You need to give exclusive previlage to xp_cmdshell proc to user if he is
not the member of symin role.
Execute permissions for xp_cmdshell default to members of the symin fixed
server role, but can be granted to other users.
Note:
If you choose to use a Windows NT account that is not a member of the local
administrator's group to start MSSQLServer service, users who
are not members of the symin fixed server role cannot execute xp_cmdshell
Thanks
Hari
SQL Serber MVP
"Malachy O'Connor" <malachyoconnor2@.o2.ie> wrote in message
news:1113392109.360561.53050@.g14g2000cwa.googlegroups.com...
>I have a stored procedure sp_LogError that it self executes the
> xp_cmdShell procedure.
> When I execute the procedure using a 'normal' acount with only public
> acces to the database i get an error:
> EXECUTE permission denied on object 'xp_cmdshell', Database 'master',
> owner 'dbo'
> sp_LogError is owned by dbo and all users have permission to access it.
> It runs fine under the sa account.
> What do I need to do so that all users can execute sp_LogError without
> granting all users access to xp_cmdShell.
> Thanks In Advance
> Malachy
>|||Thanks for that Hari.
It is just that I was hoping not to have to give access to xp_cmdshell
to all users that wished to use my sp. I|||I was able to create my own sp_logError in master which called the
xp_cmdShell. Because it was owned by dbo it had permission to execute
xp_cmdShell.
I was then able to make sp_logError public so everyone can access it
without needing explicit access to xp_cmdShell.

grant object permissions in stored procedure

I am lazy. I thought that I would get this out in the open from the outset.
Now, that said, I will justify it. I like to set up little routines that do
all of the things that I keep forgetting to do, such as setting the
permissions on new stored procedures that I add to a database.
I have a problem however; I cannot use the 'grant execute on myobject to
user' with variables.
Consider the following taken from my database permissions setup script ...
...
while (@.@.fetch_status = 0) begin
-- Set the owner to dbo if not already ...
if (@.objectUid <> @.dboUid)
execute sp_changeobjectowner @.objname=@.objectName,
@.newowner='dbo'
-- Grant public access permission.
grant execute on @.objectName to public
fetch next from objectNames into @.objectName, @.objectUid
end
...
This code generates an invalid syntax error on the lise 'grant execute ...'.
I have also tried creating a variable containing the command with the
variables expanded and using exec[ute] to execute the command. This also
fails as exec[ute] "Executes a scalar-valued, user-defined function, a
system procedure, a user-defined stored procedure, or an extended stored
procedure. Also supports the execution of a character string within a
Transact-SQL batch." and I read this (along with the error messages when I
tried it anyway) to mean that TSQL statements are not included which
suprises me as I am sure that I have exec[ute]d 'select ...' commands
before!
Does anybody have any suggestions as to how I can execute the grant
statement within the loop as shown above.
Any help will be gratefully accepted; I would hate to have to set the
permissions manually!"Martin Robins" <martin - robins @. ntlworld dot com> wrote in message
news:ek85i1WlDHA.2436@.TK2MSFTNGP09.phx.gbl...
> I am lazy. I thought that I would get this out in the open from the
outset.
> Now, that said, I will justify it. I like to set up little routines that
do
> all of the things that I keep forgetting to do, such as setting the
> permissions on new stored procedures that I add to a database.
> I have a problem however; I cannot use the 'grant execute on myobject to
> user' with variables.
> Consider the following taken from my database permissions setup script ...
> ...
> while (@.@.fetch_status = 0) begin
> -- Set the owner to dbo if not already ...
> if (@.objectUid <> @.dboUid)
> execute sp_changeobjectowner @.objname=@.objectName,
> @.newowner='dbo'
> -- Grant public access permission.
> grant execute on @.objectName to public
> fetch next from objectNames into @.objectName, @.objectUid
> end
> ...
> This code generates an invalid syntax error on the lise 'grant execute
...'.
> I have also tried creating a variable containing the command with the
> variables expanded and using exec[ute] to execute the command. This also
> fails as exec[ute] "Executes a scalar-valued, user-defined function, a
> system procedure, a user-defined stored procedure, or an extended stored
> procedure. Also supports the execution of a character string within a
> Transact-SQL batch." and I read this (along with the error messages when I
> tried it anyway) to mean that TSQL statements are not included which
> suprises me as I am sure that I have exec[ute]d 'select ...' commands
> before!
> Does anybody have any suggestions as to how I can execute the grant
> statement within the loop as shown above.
> Any help will be gratefully accepted; I would hate to have to set the
> permissions manually!
>
exec('grant execute on ' + @.objectName + ' to public')
It sounds like you're doing this already, so maybe it's just a typo. You
might consider writing your script like this, as it makes troubleshooting
much easier:
set @.sql = 'grant execute on ' + @.objectName + ' to public'
if @.debug = 1 print @.sql
else exec(@.sql)
Add a @.debug parameter to your procedure/script, and you can easily check
that your code is doing what you think it is.
Simon|||Thankyou Simon.
I did not have a typo as such, more a lack of knowledge.
my exec[ute] statement was:
set @.grantStatement = N'grant execute on [' + @.objectName +N'] to
[public]'
exec @.grantStatement
This was generating the error "The name 'grant execute on
[BrowseAddressesByCompany] to [public]' is not a valid identifier.", however
by putting in the brackets as shown in your example that allowed the
statement to execute.
Cheers.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f912ea1$1_2@.news.bluewin.ch...
> "Martin Robins" <martin - robins @. ntlworld dot com> wrote in message
> news:ek85i1WlDHA.2436@.TK2MSFTNGP09.phx.gbl...
> > I am lazy. I thought that I would get this out in the open from the
> outset.
> > Now, that said, I will justify it. I like to set up little routines that
> do
> > all of the things that I keep forgetting to do, such as setting the
> > permissions on new stored procedures that I add to a database.
> >
> > I have a problem however; I cannot use the 'grant execute on myobject to
> > user' with variables.
> >
> > Consider the following taken from my database permissions setup script
...
> >
> > ...
> > while (@.@.fetch_status = 0) begin
> >
> > -- Set the owner to dbo if not already ...
> > if (@.objectUid <> @.dboUid)
> > execute sp_changeobjectowner @.objname=@.objectName,
> > @.newowner='dbo'
> >
> > -- Grant public access permission.
> > grant execute on @.objectName to public
> >
> > fetch next from objectNames into @.objectName, @.objectUid
> > end
> > ...
> >
> > This code generates an invalid syntax error on the lise 'grant execute
> ...'.
> >
> > I have also tried creating a variable containing the command with the
> > variables expanded and using exec[ute] to execute the command. This also
> > fails as exec[ute] "Executes a scalar-valued, user-defined function, a
> > system procedure, a user-defined stored procedure, or an extended stored
> > procedure. Also supports the execution of a character string within a
> > Transact-SQL batch." and I read this (along with the error messages when
I
> > tried it anyway) to mean that TSQL statements are not included which
> > suprises me as I am sure that I have exec[ute]d 'select ...' commands
> > before!
> >
> > Does anybody have any suggestions as to how I can execute the grant
> > statement within the loop as shown above.
> >
> > Any help will be gratefully accepted; I would hate to have to set the
> > permissions manually!
> >
> >
> exec('grant execute on ' + @.objectName + ' to public')
> It sounds like you're doing this already, so maybe it's just a typo. You
> might consider writing your script like this, as it makes troubleshooting
> much easier:
> set @.sql = 'grant execute on ' + @.objectName + ' to public'
> if @.debug = 1 print @.sql
> else exec(@.sql)
> Add a @.debug parameter to your procedure/script, and you can easily check
> that your code is doing what you think it is.
> Simon
>

Grant Execute!

Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
AdamIf you have SQL Server 2005, you can GRANT EXECUTE on a schema. If you have
SQL 2000, you'd have to do the GRANT's separately. Ideally, you should
grant only to a role and then add users to the role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uMljqe%23PGHA.2888@.tk2msftngp13.phx.gbl...
Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
Adam|||Hi,
http://www.codeproject.com/database/T-SQL.asp
HTH, Jens Suessmeyer.|||Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Roy Harvey
Beacon Falls, CT
On Sun, 5 Mar 2006 09:59:09 +1000, "Mr Ideas Man"
<adam@.pertrain.com.au> wrote:

>Hi all,
>One of my workmates has acciddently changed the EXECUTE permissions for my
>main login.
>Is their a System stored procedure or something i can do to give Execute
>permissions on all stored procedures in my DB
>to a particular user, without having to do each procedure individually.
>Cheers,
>Adam
>

Grant Exec to all UDFs and Stored Procedures

Hi All,
I am little at a loss here. I found a procedure that allows me to
pass two parameters: username and dbname and it grants execute to that
user to all stored procedures on theat specific database.
I couldn't find anything that would allow me to grant execute
permissions on all UDFs as well for a specific user.
Is there anything that I can use?
thank you,
T.Same as you do for a stored procedure. you can grant execute permission only
on scalar udfs.
grant execute on <schema.udf_name> to <database_principal>
AMB
"tolcis" wrote:
> Hi All,
> I am little at a loss here. I found a procedure that allows me to
> pass two parameters: username and dbname and it grants execute to that
> user to all stored procedures on theat specific database.
> I couldn't find anything that would allow me to grant execute
> permissions on all UDFs as well for a specific user.
> Is there anything that I can use?
> thank you,
> T.
>

GRANT EXEC Problem

Hi,
SQL Server does not like my stored procedure below. It complains
about the GRANT EXEC line
What am I doing wrong?
JD
----
DECLARE @.PROCNAME varchar(50)
DECLARE cPROCNAME CURSOR FOR
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE = 'P' AND CATEGORY = 0
ORDER BY NAME
OPEN cPROCNAME
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME /* Prime the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN
GRANT EXEC ON @.PROCNAME TO PUBLIC <--Problem here
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME
END
CLOSE cPROCNAME
DEALLOCATE cPROCNAMEYou cannot do a grant on a variable. You'll have to use dynamic SQL. (BTW,
in SQL 2005, you can do a GRANT EXEC on an entire SCHEMA)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:JZLff.5127$xu.344@.fed1read01...
Hi,
SQL Server does not like my stored procedure below. It complains
about the GRANT EXEC line
What am I doing wrong?
JD
----
DECLARE @.PROCNAME varchar(50)
DECLARE cPROCNAME CURSOR FOR
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE = 'P' AND CATEGORY = 0
ORDER BY NAME
OPEN cPROCNAME
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME /* Prime the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN
GRANT EXEC ON @.PROCNAME TO PUBLIC <--Problem here
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME
END
CLOSE cPROCNAME
DEALLOCATE cPROCNAME

Grant CREATE VIEW, CREATE PROCEDURE ...

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

Hi Stefan,

If you made sure that you granted them the needed permissions, you possibly revoked/denied some permissions to them. Look at the database level, if they are able to create / alter / drop a view. Denieing overwrite any granted special rights.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

unfortunately no . I didn't revoke or deny any permissions. The specific users are members of db_owner ..and in addition I tried to grant them all permissions on the db level. There is no permission denied.

Might it be a problem that the default schema is dbo?

Regards,

Stefan

|||Which version of SQL Server are you using ? Can you post the header of your creation script here ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Besideyour original problem, why didn′t you put him in the db_ddladmin group, that should be *normally* sufficient to create objects.

HTH, Jens Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens,

I'm using SQL Server 2005 in an Enterprise Edition.

Currently I was trying to set up a very small sample db with the same conditions on a Developer Edition on my notebook ...strange thing it worked with the membership in the role db_owner. Now I'm wondering where there is the difference ...since with my last attempt I granted the user on the "server" database all available server and db level permissions.

Question:

The orignal db has been migrated (by detach and attach) from SQL Server 2000 about a week ago. Could there be any condition that settings from the old 2000 DB might have a bad influence on the migrated 2005 version?

Regards,

Stefan

|||Hi,

not directly, but if you detach and attach a db, the server logins are created by default. So you have to drop and recreate the users again OR rempa the server logins to the database users.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

this morning I was trying to compare my development db with the server db and I was not able to find the differences in the permissions. Therefor ...and to avoid any long search for possible reasons I decided to drop all development users and to reset all server and db level permissions to standard. Afterwards I started setting all the permissions after our db documentation again ...and finally I succeded.

After having reset and set all permission from scratch and recreated the users it works now.

So I assume your first assumption was correct that somewhere/somehow one or more necessary permissions have been denied.

Thank you very much for your help,

Stefan

Grant create stored procedure in a specific schema

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)Gal
Create a login with schema (give a mane) as a default . Those users will
connect with this login
"" <@.discussions.microsoft.com> wrote in message
news:1FD9FD0F-097C-4B3C-9E07-48D0D1AB4D87@.microsoft.com...
>I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which
> role
> let the login make ddl changes only in on schema (not in the dbo schema)

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.
Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>
|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '' "
Please note that same name exist for login and database user i.e CORP\AppDev
.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '' "
> Please note that same name exist for login and database user i.e CORP\AppD
ev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
>

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >

Grant alter procedure kind of thing

Hey guys. I need to let a developer alter procedures but not create any new
procedures. Is there a way i can do it?
I don't want to
grant create procedure to accountName
Instead I want to
grant alter procedure to accountName
Please let me know if it's Possible. Thank You.Tejas Parikh,
You can check BOL and see the permissions for both statements. The "alter
procedure" permission is not transferable and just members of symin,
db_owner, db_ddladmin and the sp owner have permission to alter the sp.
AMB
"Tejas Parikh" wrote:

> Hey guys. I need to let a developer alter procedures but not create any n
ew
> procedures. Is there a way i can do it?
> I don't want to
> grant create procedure to accountName
> Instead I want to
> grant alter procedure to accountName
> Please let me know if it's Possible. Thank You.
>|||>> I need to let a developer alter procedures but not create any new
procedures.<<
What the heck'!!! Let's give all the teenagers car keys and whiskey.|||LOL... are you saying the programmers are bad?
Grant
Who gives a {censored} if I am wrong.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>
More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..
Truly that's a bad idea. If your developers are qualified to write stored
procedures, then you should let them decide how to structure the code and
add procedures as necessary.
David|||Thanks Alejandro and David for your reply. It gives me the answer. All, I wa
s
trying to say is I dont want them to add any more sp's, just alter them if
needed. But well, u have a point, David.
Thank you for all your help.|||This really does show just out of touch you are with out SQL Server is used
within industry.
Do you think every shop has a DBA writing database designs and stored
procedures?
Seriously, stop what you are doing and go and get a job as a junior
programmer and get some very needed industrial experience, it looks like you
are too class room bound and have little if any (probably the latter)
exposure to business.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||No, but like teenagers, HIGHLY SUSPECT! I have been both in my
lifetime, so I now these things :)|||>> More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..<<
As the adoptive father of "troubled teenagers" and the legal
grandfather of two bastards, they forget the condom when they are in a
hurry. I am not going to put a :) on that one. Much like constraints
on a database, I should have given them birth control shots ...|||>> Do you think every shop has a DBA writing database designs and stored pro
cedures? <<
NO! I assume that bad programmers, like you, are writing schemas and
stored procedures. A large part of my consulting is based on cleaning
up the mess.
I would hope that a GOOD shop has code reviews and teaches the novice
programmers how to write SQL.
My publishers would not like that :)
My first full-time paid programing job was in 1965; I was a GS-1 at the
Pittman-Dunn Research Labs in Philadelphia. When did you start on your
full-time paid programing job? You never worked your way from "code
monkey" to "guru", did you?
I will not be in a classroom again until April. I have a two w gig
for a Seimens company in South America. Do you ever leave the UK?
Leave your own company? Your own department within the company? Your
own team within that department?

Grabbing characters from a string

Hello

I want to write a stored procedure (using Enterprise Manager) that can grab
the digits that are inbetween the two dashes (-) in strings like:
123-150-40
1-123-8
32-4215-61

The digits to the left, right and inbetween the dashes could be any length,
so a static "get the 5th, 6th and 7th digit" stored procedure won't work.

Many thanks,

--
Chris Michael
www.INTOmobiles.com
Download 100s of ringtones, wallpapers & logos every month for only 1.50
per weekChris Michael (news@.intomobiles.com) writes:
> I want to write a stored procedure (using Enterprise Manager) that can
> grab the digits that are inbetween the two dashes (-) in strings like:
> 123-150-40
> 1-123-8
> 32-4215-61
> The digits to the left, right and inbetween the dashes could be any
> length, so a static "get the 5th, 6th and 7th digit" stored procedure
> won't work.

And you want the result to be? Do you want:

12315040
11238
32421561

That is, one single number formed? That would be easy with help of
the replace() function.

Or do you want triplets like:

123, 150, 40
1, 123, 8
32, 4215, 61

And in such case, is there always exactly two dashes, or can you have

123-3455-2345-23345-2349-2-23

If you always have two dashes, using a combination of substring(),
patindex(), reverse(), right() and left() might do the trick.

All functions I have mentioned here, are listed in Books Online under
Functions, String Functions.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Grab IDENTITY from called stored procedure for use in second stored procedure in ASP.NET p

I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @.@.IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck:

Public Sub InsertOrder()

Conn.Open()

cmd = New SqlCommand("Add_NewOrder", Conn)
cmd.CommandType = CommandType.StoredProcedure

' pass customer info to stored proc
cmd.Parameters.Add("@.FirstName", txtFName.Text)
cmd.Parameters.Add("@.LastName", txtLName.Text)
cmd.Parameters.Add("@.AddressLine1", txtStreet.Text)
cmd.Parameters.Add("@.CityID", dropdown_city.SelectedValue)
cmd.Parameters.Add("@.Zip", intZip.Text)
cmd.Parameters.Add("@.EmailPrefix", txtEmailPre.Text)
cmd.Parameters.Add("@.EmailSuffix", txtEmailSuf.Text)
cmd.Parameters.Add("@.PhoneAreaCode", txtPhoneArea.Text)
cmd.Parameters.Add("@.PhonePrefix", txtPhonePre.Text)
cmd.Parameters.Add("@.PhoneSuffix", txtPhoneSuf.Text)

' pass order info to stored proc
cmd.Parameters.Add("@.NumberOfPeopleID", dropdown_people.SelectedValue)

cmd.Parameters.Add("@.BeanOptionID", dropdown_beans.SelectedValue)
cmd.Parameters.Add("@.TortillaOptionID", dropdown_tortilla.SelectedValue)

'Session.Add("FirstName", txtFName.Text)

cmd.ExecuteNonQuery()

cmd = New SqlCommand("Add_EntreeItems", Conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.CateringOrderID", get identity from previous stored proc) <--------

Dim li As ListItem
Dim p As SqlParameter = cmd.Parameters.Add("@.EntreeID", Data.SqlDbType.VarChar)
For Each li In chbxl_entrees.Items
If li.Selected Then
p.Value = li.Value
cmd.ExecuteNonQuery()
End If
Next

Conn.Close()

I want to somehow grab the @.CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)

Sorry that I don't know the exact syntax off hand, but if your stored procedure is returning the @.@.Identity as it's return, then you need to add the return parameter to the first command.

It'd be something like (sorry again, this is from memory):

cmd.Parameters.Add(New SqlParameter("@.CateringOrderID",RETURN_VALUE)) or something similiar, then at later add

dim ret as long = cmd.Parameters("@.CateringOrderID").value

Or something very similiar.

If your stored procedure is using an output parameter, you need to do the same thing, but instead of calling it RETURN_VALUE, it's like OUTPUT_VALUE or something. There is a more descriptive way of adding parameters that you can tell it if it's return value type parameter, an input type, or an output type. Once you find that, that's 90% of your solution.

|||thanks for your input, still working on it.|||

right now I'm trying to use this to obtain the returned @.@.IDENTITY from my first stored proc:

Dim NewCateringOrderIDAsInteger =CType(cmd.ExecuteScalar(),Integer)

|||Yes, that will work if the identity is the first and only result the stored procedure generates. To turn off empty resultsets (From inserts, updates, etc that go on in the stored procedure), place a SET NOCOUNT ON at the beginning of the stored procedure, and place a SET NOCOUNT OFF right before your SELECT SCOPE_IDENTITY(). Then optionally add SET NOCOUNT ON again if there are any other database modification statements after that, and finally SET NOCOUNT OFF at the very end.|||

Here we go, thanks for the start

http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

|||You can do something like this:

SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand("sproc_name",myConnection);

// Attach the parameters to the myCommand object

Set the parameter direction of the commad object to returnValue

myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

int identity = (int) myCommand.Parameters["MyReturnValue"].value;

// now that you have identity you can send into next stored procedure

Also remember that you can also send the values from one stored procedure to another stored procedure using T-SQL queries this way you dont need to return the identity to the presentation layer.

CREATE PROCEDURE #test_proc
AS
INSERT INTO #test VALUES(123)

RETURN SCOPE_IDENTITY()|||Here is another way which simply calls one stored procedure from another stored procedure:

CREATE TABLE #Names (k1 int identity(1,1) , name varchar(20) )

CREATE TABLE #Phone (k2 int identity(1,1), phone varchar(20), k1 int )

CREATE PROCEDURE #insert_name

@.Name varchar(20)

AS

DECLARE @.returnValue int

INSERT INTO #Names VALUES(@.Name)

SET @.returnValue = SCOPE_IDENTITY()

EXEC #insert_phone @.nameID = @.returnValue

EXEC #insert_name @.Name = 'AzamSharp'

SELECT * FROM #Names
SELECT * FROM #phone|||thanks but in this case, I'm not able to send the IDENTITY to another stored procedure because I need to run through another stored proc to insert checkboxlist items and must do this through another call where I loop through each item in the checkbox. I suppose it would be much better to do what you say and just loop through the checkboxlist items first then pass one string to the same stored procedure...actually I'll try that way instead this time since I do know how to pass the identity or other parameters to other stored procs from within the same stored proc|||

Create proc ( int @.id Output) AS

Select * from table ;

Select @.id=@.@.identity;

===============================

cmd.Parameters.Add(@.id,SqlDbType.Int32);

cmd.Parameters["@.id"].Direction=ParameterDirection.Output;

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

return (int)cmd.Parameters["@.id"].value;

============================

i am not fimiliar with english, may be missing spell but here is a idea which i can work through out.

GOT IT...Re: Enabling service broker

Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>Moving system datbases ([master], [model], [temp] and [msdb]
) between
instances of the is never proper, no matter the steps involved.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msd
b]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>

Got it!

Thanks Tibor...
ALTER procedure admin_ConvertUnix2Dos
as
declare @.dir varchar(256)
declare @.FileName varchar(256)
declare @.Convert varchar(512)
declare @.Exec varchar(512)
create table #tmp
(FileName varchar(256))
set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /B
'
insert into #tmp exec master..xp_cmdshell @.dir
declare MyCur cursor for
select FileName from #tmp
open MyCur
fetch next from MyCur into @.FileName
while @.@.fetch_status = 0
begin
set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B
',@.FileName+'"')
set @.Convert = replace(@.Convert,'dir','')
exec master..xp_cmdshell @.Convert
fetch next from MyCur into @.FileName
end
close MyCur
deallocate MyCur
"ChrisR" <noemail@.bla.com> wrote in message
news:e7b%23CC3oFHA.2080@.TK2MSFTNGP14.phx.gbl...
> I've been messing with the quotes and am getting nowhere quickly. Any
> ideas?
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eYhBM12oFHA.3828@.TK2MSFTNGP12.phx.gbl...
>You removed the double quotes around the path of the EXE file? I think the p
roblem is that when you
have two sets of double quotes (as in the first version), you need to enclos
e the hole shebang in
double quotes:
""c:\Unix2Dos\Unix2Dos.exe" "C:\Documents and Settings\chris.rose\My
Documents\FTP\CABHLDRLSACTNCONSTANTS.TAB;1""
Not needed now as you don't have spaced etx in path to Unix2Dos.exe, but mig
ht be worth knowing for
next time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:uAmBsL3oFHA.3996@.TK2MSFTNGP12.phx.gbl...[c
olor=darkred]
> Thanks Tibor...
>
> ALTER procedure admin_ConvertUnix2Dos
> as
> declare @.dir varchar(256)
> declare @.FileName varchar(256)
> declare @.Convert varchar(512)
> declare @.Exec varchar(512)
> create table #tmp
> (FileName varchar(256))
> set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /
B '
> insert into #tmp exec master..xp_cmdshell @.dir
> declare MyCur cursor for
> select FileName from #tmp
> open MyCur
> fetch next from MyCur into @.FileName
> while @.@.fetch_status = 0
> begin
> set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B ',@.FileName+
'"')
> set @.Convert = replace(@.Convert,'dir','')
> exec master..xp_cmdshell @.Convert
> fetch next from MyCur into @.FileName
> end
> close MyCur
> deallocate MyCur
>
> "ChrisR" <noemail@.bla.com> wrote in message news:e7b%23CC3oFHA.2080@.TK2MSF
TNGP14.phx.gbl...
>[/color]

got error when using stored procedure with temp table in it

I got an error message when i created a dataset using a stored procedure with temp table in it. The error is:

"could not generate a list of fields for the query. check the query syntax or click the refresh fields on the query toolbar. invalid object name ' #indented' ".

after I click on the refresh button, everything looks fine. But after I put the fields in the table, and go to the Preview, it is a blank report.

Does anybody know what happened?

3x a lot!

Your problem occurs because the sproc isn't returning metadata (column descriptions, etc.) from your temp table dependably -- Nothing that you did wrong, we just can't get meta data from an object which doesn't exist yet...The way we normally get metadata back from sprocs doesn't work when they return data from a temp table.

There are two thing you can try, although I think you've already done #1...

#1. Use the generic query designer, click "Refresh Fields", and you'll get a little dialog asking you for paramter info for your sproc...provide it.. finish your work, cross your fingers, move to layout view and then Preview

#2. In your sproc, return your data using a table variable vs. Select * from ##SomeTempTable

From what I read, you'll actually only have this problem in the designer...if you went ahead and plugged in the correct RDL for your fields manually and then deployed the report to the server, it would run fine...

|||Thank you!|||I also get an error because of a temp table, but this happens when creating data-driven subscription query: The

dataset cannot be generated. An error occurred while connecting to a

data source, or the query is not valid for the data source.

(rsCannotPrepareQuery) Invalid object name '##XX'. Can you help me with

this?|||

Unfortunately, you're running into a behavior you're not going to able to avoid...if you try and set up the subscription via Report Manager it (Report Manager) will call "PrepareQuery" (which we would expect to fail when dealing with temp tables) ...That's why you get your error message.

If you're really serious about using the temp table, you can still get a data-driven subscription working, BUT the price that you pay is you'll have to do it via code, using the Web Service API to do the work...If you go directly against the web service, you bypass Report Manager calling (and then failing against) "PrepareQuery"...and even when/if you get this working, you'll NEVER be able to edit the subscription in the UI, or you'll get the same errors again. So, you really should just try and whack the use of #temp tables in your scenario. :)

|||Thank you for your

answer. My temporary solution was this: when I validate the query I get

the error described above however if I

run the same stored procedure that is used in a query in let's say

management studio query and leave it open, the query in data driven

subscription web interface is validated succesfully. It works for now,

but I will try to get rid of the temp tables.

|||Ha! Great idea...Never would have thought of this!

Google Like Full Text Search

Hello,

I have a Full-Text Catalog that is populated by various columns in a few different tables. I have been able to create a stored procedure that will search across all of the different full-text columns and return me the results.

My problem is, if the someone searches for
hello world
then to my understanding I want to use FREETEXTTABLE to return my results (I actually get 0 results if I use CONTAINSTABLE)

If someone searches for
"hello world"
then I want to use CONTAINSTABLE because FREETEXTTABLE returns too many results.

And now the biggest problem would be, if someone searches for
"hello world" program
I would somehow need to use CONTAINSTABLE for the phrase and FREETEXTTABLE for 'program'. The SQL to accomplish something like this would probably be very ugly (if possible at all)

Can anyone give me any suggestions on this matter? I'm trying to create a google like search on a database which contains text and files (as BLOBs).

Thanks in advanceI should clarify a bit more.

If someone searches for
"hello world"
then I want to use CONTAINSTABLE because FREETEXTTABLE returns too many results.

The problem isn't that FREETEXTTABLE returns too many results but rather the rankings that I get back aren't helpful. Say I have 2 completely different tables and I want to search across both of them. Table1 has rows that contain the phrase "hello world" while Table2 doesn't contain that specific phrase, but does contain the separate words 'hello' and 'world'. I run FREETEXTTABLE on both Table1 and Table2, Union the results, and then return the table to my web app (where other formatting occurs before the results are displayed to the user.) Unfortunately because of how the Ranks are calculated, the entries in Table2 have a higher Rank than the entries in Table1 (even though the exact phrase occurs in Table1)

I want the rows from Table1 to appear before Table2 in this case.

One solution that I have been playing with is to always use CONTAINSTABLE, use regular expressions to insert 'AND' where applicaple, and use the 'FORMSOF(INFLECTIONAL, @.searchStr)' option. Unfortunately this seems to break when someone searches for
hello world
because I would insert an 'AND' between the words and then try to find the inflectional forms of "hello AND world" which of course breaks.

Once again, and ideas/suggestions would be greatly appreciated|||I think I've found the solution

If someone wants to search for...
"hello world" program

Then the sql would look like this...
SELECT *
FROM CONTAINSTABLE(T_Table, *, 'FORMSOF(INFLECTIONAL, "hello world") AND FORMSOF(INFLECTIONAL, "program")')|||Thanks EvilMonkey - very helpful. =)|||

cool but can u give some details.

Good SQL Book

hello

i am just starting to learn sql and know the basics, but now im looking for a good book to learn some more. A book that covers stored procedure would be very useful. If possible a book with q and a would be very good because i feel this tests if u understand what was just explaned. but if there is a good book without this it is ok. All sugestions welcome

NubNub

I'd just go on amazon and search for "T-SQL". Most books will be the same because the underlying concepts are all very similar. Personally I go to the W3's tutorial websites for everything but then again I am too cheap for books.