Excel Import: user

Showing posts with label user. Show all posts

Thursday, March 29, 2012

Executable or Way for User to Launch an SQL Package?

I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?Howdy

If its through a web or VB type app, let the web app execute a stored procedure called by the app.

Cheers,

SG.|||Hey,
from a vb app or vb script you can do this function:

Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String

Set oPKG = New DTS.Package

' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

End Sub

ExecContextHit through SQL Profiler

Hello,
Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
Thanks !!
ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:

>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:

>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]

Exec Stored Procedures with high level user permissions

I need to run a stored procedure with admin rights inside the stored procedure like so

Exec xp_cmdshell "c:\start.exe"

i need this to have administrator access so when it is called upon by an asp.net web page that executes the stored procedure it is not run by Network Service user but Administrator. How would i do this.it's the account that runs sqlserver serice that you need|||i am sorry i still don't understand if you could give an example stored procedure or

are you stating that the user that stored the stored procedure is the user level it is run at.|||ohh i forgot to add thank you for replying so fast last time|||what i am saying is that you need to make sure that the account that is used to start sqlserver service is an account that is given enough priveleges to perform the task you need.|||so if the service is started by user Administrator which it alwasy is. That is the user used to start any programs in the stored procedure.

Tuesday, March 27, 2012

EXEC SQL CONNECT TO

We tried to use the embedded “EXEC SQL CONNECT TO servername.dbname USER u
name.password” but we kept getting the error message: “Server: Msg 156,
Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.”
We tried the upper / lower case of the “connect to”, with / without the
" around the server name, database name, user name, but none of them work. W
hat did we do wrong?
Thanks in advance for your help.This does not in any way seem to be valid SQL Server SQL language construct.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.pas
sword" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around t
he server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>|||This is how it documented in the BOL or the URL below:
connect to:
_01_1g1b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_01_1g1b.asp
set connection:
_01_5pda.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_01_5pda.asp
"Tibor Karaszi" wrote:

> This does not in any way seem to be valid SQL Server SQL language construc
t...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> kept getting the error message: "Server: Msg 156, Level 15, State 1, Line
1
> database name, user name, but none of them work. What did we do wrong?
>
>|||I see, this is embedded SQL, supposed to be run through a precompiler. I sup
pose that you have below
inside some C-program and you get the error message when you run the c-progr
am through the
precompiler? Is that the case?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...[vbcol=seagreen]
> This is how it documented in the BOL or the URL below:
> connect to:
> rf_01_1g1b.asp" target="_blank">http://msdn.microsoft.com/library/d...
rf_01_1g1b.asp
> set connection:
> rf_01_5pda.asp" target="_blank">http://msdn.microsoft.com/library/d...
rf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
>
we[vbcol=seagreen]
name,[vbcol=seagreen]sql

EXEC SQL CONNECT TO

We tried to use the embedded â'EXEC SQL CONNECT TO servername.dbname USER uname.passwordâ' but we kept getting the error message: â'Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.â'
We tried the upper / lower case of the â'connect toâ', with / without the " around the server name, database name, user name, but none of them work. What did we do wrong?
Thanks in advance for your help.This does not in any way seem to be valid SQL Server SQL language construct...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around the server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>|||I see, this is embedded SQL, supposed to be run through a precompiler. I suppose that you have below
inside some C-program and you get the error message when you run the c-program through the
precompiler? Is that the case?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...
> This is how it documented in the BOL or the URL below:
> connect to:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlforc/ec_6_erf_01_1g1b.asp
> set connection:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlforc/ec_6_erf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
> > This does not in any way seem to be valid SQL Server SQL language construct...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> > news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> > > We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but
we
> > kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> > > Incorrect syntax near the keyword 'TO'."
> > >
> > > We tried the upper / lower case of the "connect to", with / without the " around the server
name,
> > database name, user name, but none of them work. What did we do wrong?
> > >
> > > Thanks in advance for your help.
> > >
> >
> >
> >

EXEC SQL CONNECT TO

We tried to use the embedded “EXEC SQL CONNECT TO servername.dbname USER uname.password” but we kept getting the error message: “Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.”
We tried the upper / lower case of the “connect to”, with / without the " around the server name, database name, user name, but none of them work. What did we do wrong?
Thanks in advance for your help.
This does not in any way seem to be valid SQL Server SQL language construct...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around the server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>
|||This is how it documented in the BOL or the URL below:
connect to:
http://msdn.microsoft.com/library/de...rf_01_1g1b.asp
set connection:
http://msdn.microsoft.com/library/de...rf_01_5pda.asp
"Tibor Karaszi" wrote:

> This does not in any way seem to be valid SQL Server SQL language construct...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> database name, user name, but none of them work. What did we do wrong?
>
>
|||I see, this is embedded SQL, supposed to be run through a precompiler. I suppose that you have below
inside some C-program and you get the error message when you run the c-program through the
precompiler? Is that the case?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...[vbcol=seagreen]
> This is how it documented in the BOL or the URL below:
> connect to:
> http://msdn.microsoft.com/library/de...rf_01_1g1b.asp
> set connection:
> http://msdn.microsoft.com/library/de...rf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
we[vbcol=seagreen]
name,[vbcol=seagreen]

exec sp_primarykeys and exec sp_foreignkeys

Hi,
I need to check the primary and the foreign keys of
existing user tables in one table to create the
corresponding Data Model.
I execute the following procedure, i see the BOL and i
need to enter one linked server for the procedure execute
fine. Im not doing this because im doing this in the local
machine.
exec sp_primarykeys
How can i generate this Data Model or how can i get this
relations with the minimum effort?
Best RegardsCC&JM
Try put your local sever name and remember you have to enable access data
exec sp_serveroption 'Server','data access','true'
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bded01c47a14$cec4e000$a601280a@.phx.gbl...
> Hi,
> I need to check the primary and the foreign keys of
> existing user tables in one table to create the
> corresponding Data Model.
> I execute the following procedure, i see the BOL and i
> need to enter one linked server for the procedure execute
> fine. Im not doing this because im doing this in the local
> machine.
> exec sp_primarykeys
> How can i generate this Data Model or how can i get this
> relations with the minimum effort?
> Best Regards
>

EXEC security question

I am not an expert with SQL security first of all.
I have a sql user account setup and I want to grant
database wide SP EXEC access, is there a way to do this
or do I have to grant each SP EXEC access on that users
account?On 2005, you can grant execute permissions on schema and also database level. No such option in
2000, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>I am not an expert with SQL security first of all.
> I have a sql user account setup and I want to grant
> database wide SP EXEC access, is there a way to do this
> or do I have to grant each SP EXEC access on that users
> account?|||Thanks, that makes sense.
On a related note. I have given persmission to the user to EXEC on a SP.
It works, but it keeps dropping the permission back to do not allow. So
he'll run the SP from a VBS file and it works fine, but if we try to run it
again it fails, and when we check the permissions, it is off again, ever
heard of that?
"Tibor Karaszi" wrote:
> On 2005, you can grant execute permissions on schema and also database level. No such option in
> 2000, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
> >I am not an expert with SQL security first of all.
> >
> > I have a sql user account setup and I want to grant
> > database wide SP EXEC access, is there a way to do this
> > or do I have to grant each SP EXEC access on that users
> > account?
>|||> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
is gone from the fact that the user cannot perform the action anymore? First step is to see what has
happened. Then one might investigate why or who did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
> Thanks, that makes sense.
> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
> "Tibor Karaszi" wrote:
>> On 2005, you can grant execute permissions on schema and also database level. No such option in
>> 2000, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
>> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>> >I am not an expert with SQL security first of all.
>> >
>> > I have a sql user account setup and I want to grant
>> > database wide SP EXEC access, is there a way to do this
>> > or do I have to grant each SP EXEC access on that users
>> > account?
>>|||After we received he permission denied error we went into enterprise mngr and
the EXEC privlege was cleared. Where as before it was checked. I don't see
how or why it would clear itself?
"Tibor Karaszi" wrote:
> > On a related note. I have given persmission to the user to EXEC on a SP.
> > It works, but it keeps dropping the permission back to do not allow. So
> > he'll run the SP from a VBS file and it works fine, but if we try to run it
> > again it fails, and when we check the permissions, it is off again, ever
> > heard of that?
> Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
> is gone from the fact that the user cannot perform the action anymore? First step is to see what has
> happened. Then one might investigate why or who did it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
> > Thanks, that makes sense.
> >
> > On a related note. I have given persmission to the user to EXEC on a SP.
> > It works, but it keeps dropping the permission back to do not allow. So
> > he'll run the SP from a VBS file and it works fine, but if we try to run it
> > again it fails, and when we check the permissions, it is off again, ever
> > heard of that?
> >
> > "Tibor Karaszi" wrote:
> >
> >> On 2005, you can grant execute permissions on schema and also database level. No such option in
> >> 2000, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> >> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
> >> >I am not an expert with SQL security first of all.
> >> >
> >> > I have a sql user account setup and I want to grant
> >> > database wide SP EXEC access, is there a way to do this
> >> > or do I have to grant each SP EXEC access on that users
> >> > account?
> >>
> >>
>|||Maybe there is a REVOKE or DENY coded into the procedure?
I've seen procedure developers mistakenly put a GRANT or REVOKE
statement at the end of their proc (overlooking the need for a BEGIN
END block or a GO statement).

EXEC security question

Monday, March 26, 2012

exec proc premission

Hi
I created a user called development in sql server 2005 and make it a member
of datareader,datewriter and ddladmin role. now I created a proc under that
development account. however when I try to exec that proc, its give a
permission denied error. How come? it was able to create proc, but not
exec, its there something I have to do?
thanksPermission denied on what object? Does the proc manipulate some table,
view or proc that the development user does not have permissions to
manipulate? Are there explicit deny permissions for the development
user on some objects in the DB? Can you post actual code and actual
error messages?
*mike hodgson*
http://sqlnerd.blogspot.com
paulixml wrote:

>Hi
>I created a user called development in sql server 2005 and make it a member
>of datareader,datewriter and ddladmin role. now I created a proc under tha
t
>development account. however when I try to exec that proc, its give a
>permission denied error. How come? it was able to create proc, but not
>exec, its there something I have to do?
>thanks
>
>|||hi
here what I did, I create the prc connected as development,
create proc [dbo].[getpivot1_prc]as
select * from pivot_tmp
then I try to exec it after I create, I get this
Msg 229, Level 14, State 5, Procedure getpivot1_prc, Line 1
EXECUTE permission denied on object 'getpivot1_prc', database 'cap2005',
schema 'dbo'.
thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:uRc3wnoaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Permission denied on what object? Does the proc manipulate some table,
> view or proc that the development user does not have permissions to
> manipulate? Are there explicit deny permissions for the development
> user on some objects in the DB? Can you post actual code and actual
> error messages?
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> paulixml wrote:
>
>|||The db_ddladmin role allowed you to create the proc as a different user
(dbo) but since you explicitly stated the owner of that proc when you
created it, the development user is not the owner and therefore doesn't
have permissions to execute it. And being a memory of db_datareader,
db_datawriter & db_ddladmin does not imply execute permissions on that
new proc.
Either create the proc as development.getpivot1_prc (CREATE PROC
development.getpivot1_prc AS ...), which would make the development user
the owner of the proc and therefore imply execute permissions for the
development user, or explicitly grant execute permissions on that proc
to the development user or a role that that user is a member of (but you
can't change the permissions with the development user because
db_ddladmin does not allow you to run GRANT statements).
Why are you using the development user to create a proc owned by the dbo
user?
*mike hodgson*
http://sqlnerd.blogspot.com
paulixml wrote:

>hi
>here what I did, I create the prc connected as development,
>create proc [dbo].[getpivot1_prc]as
>select * from pivot_tmp
>then I try to exec it after I create, I get this
>Msg 229, Level 14, State 5, Procedure getpivot1_prc, Line 1
>EXECUTE permission denied on object 'getpivot1_prc', database 'cap2005',
>schema 'dbo'.
>thanks
>"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
>news:uRc3wnoaGHA.1200@.TK2MSFTNGP03.phx.gbl...
>
>
>

Exec permissions and selecting from other databases

I have SQL2000 with a couple of databases on it (same instance) - e.g. DB1 a
nd DB2 say.
In DB2 I have assiged EXEC Permissions for a user on a Stored Proc that chec
ks against a table in DB1 (e.g.)
--In DB2 Stored proc
SELECT * FROM Customer
WHERE CustomerID IN (SELECT CustomerID FROM DB1.dbo.OtherCustomers)
However the user gets an error stating that they do not have select permissi
ons on the other table (Othercustomers in db1)
Is there any way I can assign permissions just on this stored procedure (in
db2) without giving select permissions
for the user in DB1 ?
Thanks
GerryYour objects need to be owned by the same login in order to maintain an
unbroken ownership chain so that users don't need permissions on indirectly
referenced objects. You can map the 'dbo' user to the same login in both
databases by specifying the same database owner. For example:
USE DB1
EXEC sp_changedbowner 'MyLogin'
GO
USE DB2
EXEC sp_changedbowner 'MyLogin'
GO
Additionally, you need to enable cross-database chaining in both databases.
This option was introduced in SQL 2000 SP3. For example:
EXEC sp_dboption 'DB1', 'db chaining', true
EXEC sp_dboption 'DB2', 'db chaining', true
See the cross-database ownership chaining in the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0108492C-77C3-4694-9F2E-C824675D7B03@.microsoft.com...

quote:

> I have SQL2000 with a couple of databases on it (same instance) - e.g. DB1

and DB2 say.

quote:

> In DB2 I have assiged EXEC Permissions for a user on a Stored Proc that

checks against a table in DB1 (e.g.)

quote:

> --In DB2 Stored proc
> SELECT * FROM Customer
> WHERE CustomerID IN (SELECT CustomerID FROM DB1.dbo.OtherCustomers)
> However the user gets an error stating that they do not have select

permissions on the other table (Othercustomers in db1)

quote:

> Is there any way I can assign permissions just on this stored procedure

(in db2) without giving select permissions

quote:

> for the user in DB1 ?
> Thanks
> Gerry
>

sql

EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. Now
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid giving
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advance
Hi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e.
dropping tables, etc).
HTH,
Adam
"David" wrote:

> I have a user in DB that has db_datareader and db_datawriter permissions. Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader &
> db_datawriter and map it to a login?
> Thanks in advance
|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance
|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah
|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegro ups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>
|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah

EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. Now
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid giving
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advanceHi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e.
dropping tables, etc).
HTH,
Adam
"David" wrote:
> I have a user in DB that has db_datareader and db_datawriter permissions. Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader &
> db_datawriter and map it to a login?
> Thanks in advance|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegroups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah

EXEC Permissions

I have a user in DB that has db_datareader and db_datawriter permissions. No
w
I want to assign EXEC permission to all the stored procedures. How can I
achieve this through database standard roles. My objective is to avoid givin
g
permission on each stored procedure the EXEC permission.
Any clean way of creating a special database role with EXEC, db_datareader &
db_datawriter and map it to a login?
Thanks in advanceHi David,
Here are a few ideas...
Best practice, for me, is to have a security script that controls the
security of all objects in the database. Every time I write a stored
procedure, the process is to adjust the security procedure prior to
completion. This way you can periodically run the the security procedure to
bring the database(s) back into compliance. This is more time consuming.
Grant 'public' permission to execute. Since all users are members of
public, any existing or new users will have the ability to execute said
procedure. I have a script that I wrote a while back that grants 'public'
execute to all procedures. If I can locate it, I will forward.
If you want a simple way out. You can add the 'user' to the db_owner role.
This is not a safe option considering how costly db_owner access can be (i.e
.
dropping tables, etc).
HTH,
Adam
"David" wrote:

> I have a user in DB that has db_datareader and db_datawriter permissions.
Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid giv
ing
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
&
> db_datawriter and map it to a login?
> Thanks in advance|||David
Once you generate dynamic EXEC script to grant an EXECUTE permissions you
can save it an use again and again.
What is if tomorrow your boss tells you do DENY EXECUTE permission on some
stored procedures?
"David" <David@.discussions.microsoft.com> wrote in message
news:95A66800-22CA-4410-ABD7-03D7DD7A98A4@.microsoft.com...
>I have a user in DB that has db_datareader and db_datawriter permissions.
>Now
> I want to assign EXEC permission to all the stored procedures. How can I
> achieve this through database standard roles. My objective is to avoid
> giving
> permission on each stored procedure the EXEC permission.
> Any clean way of creating a special database role with EXEC, db_datareader
> &
> db_datawriter and map it to a login?
> Thanks in advance|||To grant exec permission to public is risky.
Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
can specify the user to whom you want to grant exec to all.
Regards
Amish Shah|||amish
I did not meant to grant it to the public, I hope OP understood me.
"amish" <shahamishm@.gmail.com> wrote in message
news:1138700452.078652.59830@.g14g2000cwa.googlegroups.com...
> To grant exec permission to public is risky.
> Look at wonder full store proc sp_granexec on www.sqldbatips.com, you
> can specify the user to whom you want to grant exec to all.
>
> Regards
> Amish Shah
>|||Uri
I replied for the answer from Goose. He replied to give permission to
public , which I feel risky.
Please dont missunderstand.
Regards
Amish Shah

exec permision for sql user

I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
Thanks.There is no builtin role for executing stored procedures. You could make the user db_owner, but that will bring in a load of other permissions that you probably would not want the user to have. In order to solve the immediate problem, you could run this query, to generate all the grant statements:

select 'grant execute on ' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + ' to role'
from INFORMATION_SCHEMA.ROUTINES|||hey, thanks for the information.
what does it mean 'SPECIFIC_SCHEMA ' and specific_name' in the above query.|||Those are the two columns you need from the INFORMATION_SCHEMA.Routines view. You should be able to cut and paste the code as is. The only thing you have to change is the name of the user or role you are granting the permissions to.

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florenceSQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florence
SQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

EXEC Command in all existing connections

If there any way to execute t-sql command in all connections in one time.
Example : if user insert a new record in table employees I like to notify
all users that are connected.
Aleksandar TalevYou could create a Trigger on the table for INSERT.
Have the Trigger fire the following :-
net send /users "New record added to table."
the /users switch will broadcast to all users connected to the server.
HTH
Ryan Waight, MCDBA, MCSE
"Aleksandar Talev" <alex@.semos.com.mk> wrote in message
news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If there any way to execute t-sql command in all connections in one time.
>
> Example : if user insert a new record in table employees I like to notify
> all users that are connected.
>
> Aleksandar Talev
>
>|||This is very helpfull
Thanks.
I also like to know can I substitute net send command with osql or bcp
(including also all users) ?
AT
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:#ShJRoFqDHA.1124@.TK2MSFTNGP09.phx.gbl...
> You could create a Trigger on the table for INSERT.
> Have the Trigger fire the following :-
> net send /users "New record added to table."
> the /users switch will broadcast to all users connected to the server.
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Aleksandar Talev" <alex@.semos.com.mk> wrote in message
> news:Ocvv1YFqDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > If there any way to execute t-sql command in all connections in one
time.
> >
> >
> > Example : if user insert a new record in table employees I like to
notify
> > all users that are connected.
> >
> >
> > Aleksandar Talev
> >
> >
> >
> >
>

Thursday, March 29, 2012

Executable or Way for User to Launch an SQL Package?

ExecContextHit through SQL Profiler

ExecContextHit through SQL Profiler

Exec Stored Procedures with high level user permissions

Tuesday, March 27, 2012

EXEC SQL CONNECT TO

EXEC SQL CONNECT TO

EXEC SQL CONNECT TO

exec sp_primarykeys and exec sp_foreignkeys

EXEC security question

EXEC security question

EXEC security question

Monday, March 26, 2012

exec proc premission

Exec permissions and selecting from other databases

EXEC Permissions

EXEC Permissions

EXEC Permissions

exec permision for sql user

exec in store procedure, what is in user define function?

exec in store procedure, what is in user define function?

EXEC Command in all existing connections

Excel Import

Blog Archive

About Me