EXECUTE AS question: permission

Showing posts with label permission. Show all posts

Tuesday, March 27, 2012

Executing Dynamic SQL with out Select Permission

I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_executesq
l
@.sql I am executing the dynamic sql, few of my procedures are getting input
parameter for table name and/or column names also. Now the database user is
modified with privileges, he has assigned only execute Permission. How to
solve this problem.You can't. If you use dynamic SQL you need permissions on the underlying
tables. Passing in table names and column names as parameters to a stored
procedure is not a good idea anyway, and the problem you have run into is
only one of the issues (see http://www.sommarskog.se/dynamic_sql.html). If
you can explain what you are actually trying to do, someone here can come up
with a better solution.
Jacco Schalkwijk
SQL Server MVP
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
>I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
>sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
> input
> parameter for table name and/or column names also. Now the database user
> is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||Unfortunatly you can't, with dynamic SQL you must have Select Permission on
the table.
If you tell us what you are tryng to do we could possible sugest an
alternative.
Peter
Do not arouse the sleeping dragon, for you are crunchy and taste good with
ketchup.
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also.
Care to explain just WHY you are doing that? The usual reasons are poor
database design and/or poor coding practices. The solution is almost always
not to do it. Dynamic SQL comes with a lot of incovenient baggage: security
vulnerabilities; performance implications; maintenance and reliability
issues; cost to develop and support.
David Portas
SQL Server MVP
--
"Prakash" wrote:

> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute sp_execute
sql
> @.sql I am executing the dynamic sql, few of my procedures are getting inpu
t
> parameter for table name and/or column names also. Now the database user i
s
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.|||I don't know if this applies in your case, but it helped avoid dynamic SQL
on a project of mine. If you need to query across multiple partitioned
tables (ex: SALES_2004, SALES_2003, etc), then consider using a partitioned
view (basically a view of unionized tables). When a new table is added, then
you can re-create the view that includes the new table reference.
"Prakash" <Prakash@.discussions.microsoft.com> wrote in message
news:F99F00EB-1F62-4CD4-8E66-A299A6192480@.microsoft.com...
> I have Procedures with Dynamic SQL, using EXEC(@.sql) or Execute
sp_executesql
> @.sql I am executing the dynamic sql, few of my procedures are getting
input
> parameter for table name and/or column names also. Now the database user
is
> modified with privileges, he has assigned only execute Permission. How to
> solve this problem.

Monday, March 19, 2012

Execute without Insert

Is there a way to let an account have execute permission on a stored
procedure but not let that stored procedure run insert , delete, or
update records. Basically only let them run or create stored
procedures that do selects.[posted and mailed, please reply in news]

HD (harlan@.elementalcomponents.com) writes:
> Is there a way to let an account have execute permission on a stored
> procedure but not let that stored procedure run insert , delete, or
> update records. Basically only let them run or create stored
> procedures that do selects.

I'm not really sure what you are asking. You could have a user which have
permissions to create procedure, but only has SELECT permissions on the
tables. In such case, the procedures of that user only perform SELECTs,
no updates.

But if you are asking if you somehow can say that a user may only execute
stored procedures that performs read-only operations, there is no way
to do this by a single setting, at least not what I can think of.

But you can of course grant execute permissions selectively. And to find
out which procedures that performs updates, you can use this select:

select name
from sysobjects o
where o.type = 'P'
and exists (select *
from sysdepends d
where d.id = o.id
and d.resultobj = 1)

However, a word of caution is that this query may not return all updating
procedures. For instance, if you create a procedure first and then the
tables it refers to, there will not be any dependencies recorded.

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

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

Wednesday, March 7, 2012

Execute sp_start_job from stored procedure

I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job? This is all run from a web page and NOT the Query Window.

The Agent is just a robot itmusthave correct permissions to run Jobs and other things replication included. So you clone admin level permissions to run it. Try the link below for SQL Server proxy account. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190698.aspx

Sunday, February 26, 2012

Execute permission lost for nonadmin user after db migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost after database migration with attach

execute permission for stored procedures

Hello SQL Experts,

we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.

A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.

The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.

In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?

The Owner of sp and table is dbo.

Thanks for your replies!You can't enable the user to only have permission to some of the data in the table. What you would need to do is have a view, and a column by which you would differentiate between different users. There is little cost associated with a view, and you can make the view owned by the user, so no one else can use it, or no one else can use it by mistake.

IE. [user].[viewname]

Cheers,
-Kilka|||While you can restrict access to columns using permissions, I don't know of any way to restrict access to rows using permissions.

I think that Kilka101 has the right idea about using a view. You may be able to construct a single view that uses User_Id() or Suser_Sname(), or you may need to resort to separate views for each user.

Keep in mind that as you scale upward, this gets a lot more complicated to manage, especially if you introduce any "third tier" processing like an application server. For two-tier applications this isn't likely to be a problem, but as you grow it can become a real problem.

-PatP|||Thanks for your replies!

I thought about a workaround with views, too. But I'm rather sure to remember that before Windows 2003 and SQL Sp3 it has been sufficient to grant a user the right to execute a procedure which performs a select on a table without having the explicit right to select from the table.

For example:

create proc sp_showusers
as
select * from users
where name= 'abc'
go

grant execute on sp_showusers to [my_user]
go

Though 'my_user' doesn't have granted the select permission on table users, he's got all data from table users by executing the sp.

I suppose it has something to do with the stricter security settings coming up with Windows 2003. Anyway, there must be a way out of it... :confused:|||Yes, when you create a stored procedure, the statements in it are checked against the security permissions of the creator. Permission to execute the procedure can be given to users with different permissions, that can then execute the procedure even though it does things such as your example select that the user couldn't do directly. Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

-PatP|||Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

Oh... okay, I guess this is exactly the point! The stored procedure I got the problems with contains some dynamic SQL... :( So I have to think about views then. ;)

Thank you very much for your help! :)

Execute Permission for Database Role

There are about 300 stored procedures and we are trying to separate EXECUTE
permission for Readers and Writers. Here is the scenario:
1) Two Domain groups created named SQLWriters and SQLReaders consisting of
respective users.
2) Two SQL logins created named SQLWriters and SQLReaders using Domain
groups SQLWriters and SQLReaders respectively. SQLWriters are in
db_datareader and db_datawriter roles and SQLReaders are in db_datareader
role only.
3) Two database roles created (a) db_executor for SQLWriters (b)
db_executor_reader for SQLReaders.
(4) db_executor has execute permission on all the stored procedures that
have Select Statements and Insert, Update, Delete Statements.
(5) db_executor_reader has execute permission on the stored procedures that
only have Select statement in the stored procedure.
When NT domain users from SQLReaders group are executing stored procedure
with Insert statement in it where their role (db_executor_reader) does not
have execute permission, it is still getting executed and doing the insert
and update. Any idea what else may be required here. Thanks.I had to explicitly dely execute premission on the SP. For some reason it
gives execute by default to all the stored procedures to all users. I can't
find documentation on why it is doing that. Will investigate more maybe one
of the MVPs can shed some light on this.
But you can check the effictive premissions to check what each Group has
access to.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fraz" wrote:

> There are about 300 stored procedures and we are trying to separate EXECUT
E
> permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures tha
t
> only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.|||Fraz (Fraz@.discussions.microsoft.com) writes:
> There are about 300 stored procedures and we are trying to separate
> EXECUTE permission for Readers and Writers. Here is the scenario:
> 1) Two Domain groups created named SQLWriters and SQLReaders consisting of
> respective users.
> 2) Two SQL logins created named SQLWriters and SQLReaders using Domain
> groups SQLWriters and SQLReaders respectively. SQLWriters are in
> db_datareader and db_datawriter roles and SQLReaders are in db_datareader
> role only.
> 3) Two database roles created (a) db_executor for SQLWriters (b)
> db_executor_reader for SQLReaders.
> (4) db_executor has execute permission on all the stored procedures that
> have Select Statements and Insert, Update, Delete Statements.
> (5) db_executor_reader has execute permission on the stored procedures
> that only have Select statement in the stored procedure.
> When NT domain users from SQLReaders group are executing stored procedure
> with Insert statement in it where their role (db_executor_reader) does not
> have execute permission, it is still getting executed and doing the insert
> and update. Any idea what else may be required here. Thanks.
Since I don't see your database, or know which version of SQL Server you
have, it's sort of difficult to say what is wrong. But I would suspect
that you at some earlier point granted execute rights to public. You can
use sp_helprotect to examine this. It could also be that users are members
of other roles and get permission this way.
To test that you have the actual setup correct, create an empty database
and set up users, procedure and permissions, and test that that works.
Then you can examine what is wrong in the target database.
In the end, DENY as Mohit suggested may be the best way, as it also
protects you against future accidents.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute permission for all sprocs

I am looking to write a script that will grant a specified user execute
access to all stored procedures, but no other permissions
Any help would be appreciated,
Craig"Craig HB" schrieb:
> I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Just add the name of the user account in the second line and run this:
declare @.user varchar(256)
set @.user = 'The name of the user account'
declare @.sp varchar(256)
declare cu cursor fast_forward for
select [name] from sysobjects where [xtype] = 'P'
open cu
fetch next from cu into @.sp
while @.@.fetch_status = 0
begin
execute('grant execute on [' + @.sp + '] to ' + @.user)
fetch next from cu into @.sp
end
close cu deallocate cu|||sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A903026B-14A6-40F0-9FB0-52906A69026C@.microsoft.com...
>I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig

EXECUTE permission deny

Any one can help me, below error messages for reference, thanks!

Exception Details:System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.

Source Error:

Line 96: cmdMid.Connection = conMid;Line 97: cmdMid.CommandText = "exec sp_insertspend '" + uid + "','" + Mid + "','" + status + "','" + spend + "'";Line 98: cmdMid.ExecuteNonQuery();Line 99: conMid.Close();Line 100:

Source File:f:\Microsoft Visual Studio 8\Web\Soccer\main.aspx.cs Line:98

Stack Trace:

[SqlException (0x80131904): EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734934 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 _Default.btnbet_Click(Object sender, EventArgs e) in f:\Microsoft Visual Studio 8\Web\Soccer\main.aspx.cs:98 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Hi there,

In SQL Server Managment Studio under Database Properties / Permissions you have to tickgrant execute permissionthe user or role you are using to connect to the database.

Execute Permission denied when running from IIS

My development environment is IIS 5.1, asp.net 2.0, Visual Web Developer 05 Express, MS Sql 2005 Express with XP Pro. I used a "stored procedure" in a webpage Formview to insert a record in a child table after inserting a record in the parent table. All went well when testing in VWD.

After deploying to remote site on same machine, I get an error

"EXECUTE permission denied on object 'usp_Insertdataset', database 'Job_Tracker_SQL', schema 'dbo'"

when trying to insert. I know that SQL Express is not suppose to support stored procedures. Is there a work around? I need to host this site on this machine for the immediate future.

Thanks

cbrcdr

I think that you are incorrect in stating that SQL Server Express does not support stored procedures. I'd be curious to see where that is documented (but, I have been proven wrong in the past!).

But, in any case, I would bet that in your development environment, you were connecting to the database as a DBO (database owner), and the same was not true for the remote site. By default, no one but a DBO is granted any permissions on database objects.

You should be able to grant execute permissions to the "Public" role, which should take care of allowing anyone who is permitted to connect to the database to execute your stored procedure. As an alternative, you can grant execute permission to invididual users too.

Execute the following SQL while connected to your database as the DBO user (i.e., from a query window or whatever mechanism you have available):

GRANT EXECUTE ON usp_InsertdatasetTO Public

|||

Jason

I saw a Feature Matrix on a Mircosoft Webpage that compared all the SQL products and it indicted that SQL Express did not support Stored Procedures. Maybe it was not up to date. I assumed it was right but you are. I used SSMSE and set the permissions on the Stored Procedure to execute for the login and it works.

Thanks, that was my last big hurdle for this phase of my application.

George

Execute permission denied on stored procedure

Hi,
A user of a VB.Net windows application gets this error message when
trying to add data. She is able to add data to other tables by
executing other stored procedures except in this 1 screen. I've checked
the database user/role that she is using and both have execute
permission for that stored procedure in error. Please help! I'm lost as
how to debug this problem.
Appreciate your inputs.
Thanks!Check to see if the use or role has DENY permissions. When both GRANT and
DENY permissions are present, DENY takes precedence. Execute sp_helprotect
'ProcName' to list all proc permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"hillcountry74" <shruthibg@.yahoo.com> wrote in message
news:1154708924.877916.202230@.m73g2000cwd.googlegroups.com...
> Hi,
> A user of a VB.Net windows application gets this error message when
> trying to add data. She is able to add data to other tables by
> executing other stored procedures except in this 1 screen. I've checked
> the database user/role that she is using and both have execute
> permission for that stored procedure in error. Please help! I'm lost as
> how to debug this problem.
> Appreciate your inputs.
> Thanks!
>

Execute permission denied on stored procedure

Hi,
A user of a VB.Net windows application gets this error message when
trying to add data. She is able to add data to other tables by
executing other stored procedures except in this 1 screen. I've checked
the database user/role that she is using and both have execute
permission for that stored procedure in error. Please help! I'm lost as
how to debug this problem.
Appreciate your inputs.
Thanks!Check to see if the use or role has DENY permissions. When both GRANT and
DENY permissions are present, DENY takes precedence. Execute sp_helprotect
'ProcName' to list all proc permissions.
Hope this helps.
Dan Guzman
SQL Server MVP
"hillcountry74" <shruthibg@.yahoo.com> wrote in message
news:1154708924.877916.202230@.m73g2000cwd.googlegroups.com...
> Hi,
> A user of a VB.Net windows application gets this error message when
> trying to add data. She is able to add data to other tables by
> executing other stored procedures except in this 1 screen. I've checked
> the database user/role that she is using and both have execute
> permission for that stored procedure in error. Please help! I'm lost as
> how to debug this problem.
> Appreciate your inputs.
> Thanks!
>

Execute permission denied on 'sp_xml_preparedocument'

Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/d... />
z_8odw.asp
>

Friday, February 24, 2012

Execute permission denied on 'sp_xml_preparedocument'

Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
>

Execute permission denied on 'sp_xml_preparedocument'

Hello,
We have hosted our .NET application on a windows 2003 server runing SQL
Server 2000. We have a stored procedure that uses
"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
is throwing the following error in one of the testing environment, but
working fine in the development environment, Any Ideas ?
Failed to update company details. Error message: EXECUTE permission
denied on object 'sp_xml_preparedocument',
database 'master', owner 'dbo'. Could not find prepared statement with
handle 0.
EXECUTE permission denied on object 'sp_xml_removedocument', database
'master', owner 'dbo'.
The statement has been terminated.
Thanks in Advance.
With Regards,
Sharath.Whoever you're logging in as in the test environment needs execute
permissions on the sp. You can easily check (and change) the permissions on
it in EM and compare to your other server. You can also change the
permissions through QA (GRANT statement).
"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
> Hello,
>
> We have hosted our .NET application on a windows 2003 server runing SQL
> Server 2000. We have a stored procedure that uses
> "'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
> is throwing the following error in one of the testing environment, but
> working fine in the development environment, Any Ideas ?
>
> Failed to update company details. Error message: EXECUTE permission denied
> on object 'sp_xml_preparedocument',
> database 'master', owner 'dbo'. Could not find prepared statement with
> handle 0.
> EXECUTE permission denied on object 'sp_xml_removedocument', database
> 'master', owner 'dbo'.
> The statement has been terminated.
>
> Thanks in Advance.
> With Regards,
> Sharath.|||Mike C# wrote:
> Whoever you're logging in as in the test environment needs execute
> permissions on the sp. You can easily check (and change) the permissions on
> it in EM and compare to your other server. You can also change the
> permissions through QA (GRANT statement).
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:O5k7YevGHHA.1804@.TK2MSFTNGP02.phx.gbl...
>>Hello,
>>
>>We have hosted our .NET application on a windows 2003 server runing SQL
>>Server 2000. We have a stored procedure that uses
>>"'sp_xml_preparedocument" to pick the XML values sent in a parameter. It
>>is throwing the following error in one of the testing environment, but
>>working fine in the development environment, Any Ideas ?
>>
>>Failed to update company details. Error message: EXECUTE permission denied
>>on object 'sp_xml_preparedocument',
>>database 'master', owner 'dbo'. Could not find prepared statement with
>>handle 0.
>>EXECUTE permission denied on object 'sp_xml_removedocument', database
>>'master', owner 'dbo'.
>>The statement has been terminated.
>>
>>Thanks in Advance.
>>With Regards,
>>Sharath.
>
>
Thanks Mike for the reply. I am not well versed with SQL commands nor I
have access to the Server thru EM, If you could please send the script
(GRANT), that would be very helpful.|||"Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Thanks Mike for the reply. I am not well versed with SQL commands nor I
> have access to the Server thru EM, If you could please send the script
> (GRANT), that would be very helpful.
USE master
GO
GRANT EXEC ON sp_xml_preparedocument TO public
GO
Of course you can change "public" above to another role/user. More
information is available in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp|||Thank you Mike. I will try that. :)
Mike C# wrote:
> "Sharath Shetty" <sharath.shetty567@.gmail.com> wrote in message
> news:%23o5NJqvGHHA.1912@.TK2MSFTNGP03.phx.gbl...
>>Thanks Mike for the reply. I am not well versed with SQL commands nor I
>>have access to the Server thru EM, If you could please send the script
>>(GRANT), that would be very helpful.
>
> USE master
> GO
> GRANT EXEC ON sp_xml_preparedocument TO public
> GO
> Of course you can change "public" above to another role/user. More
> information is available in BOL:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_8odw.asp
>

execute permission denied on schema?

Boy, this one is tangled, but I'll take a guess at what's relevant.
I create a schema to hold an XSD, use it, it's all great - in dev.
Trying to deploy to staging with stricter security, something is
wrong.
-- this works fine, executed by someone with sysadmin:
declare @.RBSchema XML
set @.RBSchema = (select * from openrowset(bulk
'D:\RC850\RB850.xsd', single_clob) as xmlData)
create xml schema collection RB850Schema as @.RBSchema
-- at some point, executing as someone with minimal privileges,
-- I get this message:
/*
EXECUTE permission denied on object 'RB850Schema',
database 'mydb', schema 'dbo'.
*/
So, it looks like I need to grant something to somebody, but this
fails:
GRANT EXECUTE ON dbo.RB850Schema TO minimaluser
GO
/*
Msg 15151, Level 16, State 1, Line 1
Cannot find the schema 'RB850Schema',
because it does not exist or you do not have permission.
*/
Now, I just (re)created it and it works, but, well, so, what's the
deal?
Thanks.
Josh
ps - complicating factors might be that this is happening in an SSIS
environment, which was working fine when it used integrated security,
but is now sticking with standard security. Also, I'm not certain
exactly where it's generated, but it may be from a dynamic SQL
statement like:
SET @.cmd = 'DECLARE @.x850 XML (RB850Schema)
SET @.x850 = (select * from openrowset(bulk '''
+ @.xmlfilename + ''' , single_clob) as xmldata);
SELECT @.X850 AS myxml;'
INSERT INTO @.mytable (myxml)
execute @.rc = sp_executesql @.cmd
(again, this works fine, until I try to limit privileges)
Answering my own question:
By clicking around under database security, selecting objects by type, and
hitting the script button on the dialog, I got:
GRANT EXECUTE ON XML SCHEMA COLLECTION::[dbo].[RB850Schema] TO [minimaluser]
GO
Don't know quite yet if it fixes everything, but at least the grant executes!
So, who exactly makes up this syntax?
Talking to himself in public yet again,
Josh
"JXStern" wrote:

> Boy, this one is tangled, but I'll take a guess at what's relevant.
> I create a schema to hold an XSD, use it, it's all great - in dev.
> Trying to deploy to staging with stricter security, something is
> wrong.
> -- this works fine, executed by someone with sysadmin:
> declare @.RBSchema XML
> set @.RBSchema = (select * from openrowset(bulk
> 'D:\RC850\RB850.xsd', single_clob) as xmlData)
> create xml schema collection RB850Schema as @.RBSchema
> -- at some point, executing as someone with minimal privileges,
> -- I get this message:
> /*
> EXECUTE permission denied on object 'RB850Schema',
> database 'mydb', schema 'dbo'.
> */
> So, it looks like I need to grant something to somebody, but this
> fails:
> GRANT EXECUTE ON dbo.RB850Schema TO minimaluser
> GO
> /*
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the schema 'RB850Schema',
> because it does not exist or you do not have permission.
> */
> Now, I just (re)created it and it works, but, well, so, what's the
> deal?
> Thanks.
> Josh
> ps - complicating factors might be that this is happening in an SSIS
> environment, which was working fine when it used integrated security,
> but is now sticking with standard security. Also, I'm not certain
> exactly where it's generated, but it may be from a dynamic SQL
> statement like:
> SET @.cmd = 'DECLARE @.x850 XML (RB850Schema)
> SET @.x850 = (select * from openrowset(bulk '''
> + @.xmlfilename + ''' , single_clob) as xmldata);
> SELECT @.X850 AS myxml;'
> INSERT INTO @.mytable (myxml)
> execute @.rc = sp_executesql @.cmd
> (again, this works fine, until I try to limit privileges)
>

execute permission denied on schema?

Boy, this one is tangled, but I'll take a guess at what's relevant.
I create a schema to hold an XSD, use it, it's all great - in dev.
Trying to deploy to staging with stricter security, something is
wrong.
-- this works fine, executed by someone with sysadmin:
declare @.RBSchema XML
set @.RBSchema = (select * from openrowset(bulk
'D:\RC850\RB850.xsd', single_clob) as xmlData)
create xml schema collection RB850Schema as @.RBSchema
-- at some point, executing as someone with minimal privileges,
-- I get this message:
/*
EXECUTE permission denied on object 'RB850Schema',
database 'mydb', schema 'dbo'.
*/
So, it looks like I need to grant something to somebody, but this
fails:
GRANT EXECUTE ON dbo.RB850Schema TO minimaluser
GO
/*
Msg 15151, Level 16, State 1, Line 1
Cannot find the schema 'RB850Schema',
because it does not exist or you do not have permission.
*/
Now, I just (re)created it and it works, but, well, so, what's the
deal?
Thanks.
Josh
ps - complicating factors might be that this is happening in an SSIS
environment, which was working fine when it used integrated security,
but is now sticking with standard security. Also, I'm not certain
exactly where it's generated, but it may be from a dynamic SQL
statement like:
SET @.cmd = 'DECLARE @.x850 XML (RB850Schema)
SET @.x850 = (select * from openrowset(bulk '''
+ @.xmlfilename + ''' , single_clob) as xmldata);
SELECT @.X850 AS myxml;'
INSERT INTO @.mytable (myxml)
execute @.rc = sp_executesql @.cmd
(again, this works fine, until I try to limit privileges)Answering my own question:
By clicking around under database security, selecting objects by type, and
hitting the script button on the dialog, I got:
GRANT EXECUTE ON XML SCHEMA COLLECTION::[dbo].[RB850Schema] TO [minimaluser]
GO
Don't know quite yet if it fixes everything, but at least the grant executes!
So, who exactly makes up this syntax?
Talking to himself in public yet again,
Josh
"JXStern" wrote:
> Boy, this one is tangled, but I'll take a guess at what's relevant.
> I create a schema to hold an XSD, use it, it's all great - in dev.
> Trying to deploy to staging with stricter security, something is
> wrong.
> -- this works fine, executed by someone with sysadmin:
> declare @.RBSchema XML
> set @.RBSchema = (select * from openrowset(bulk
> 'D:\RC850\RB850.xsd', single_clob) as xmlData)
> create xml schema collection RB850Schema as @.RBSchema
> -- at some point, executing as someone with minimal privileges,
> -- I get this message:
> /*
> EXECUTE permission denied on object 'RB850Schema',
> database 'mydb', schema 'dbo'.
> */
> So, it looks like I need to grant something to somebody, but this
> fails:
> GRANT EXECUTE ON dbo.RB850Schema TO minimaluser
> GO
> /*
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the schema 'RB850Schema',
> because it does not exist or you do not have permission.
> */
> Now, I just (re)created it and it works, but, well, so, what's the
> deal?
> Thanks.
> Josh
> ps - complicating factors might be that this is happening in an SSIS
> environment, which was working fine when it used integrated security,
> but is now sticking with standard security. Also, I'm not certain
> exactly where it's generated, but it may be from a dynamic SQL
> statement like:
> SET @.cmd = 'DECLARE @.x850 XML (RB850Schema)
> SET @.x850 = (select * from openrowset(bulk '''
> + @.xmlfilename + ''' , single_clob) as xmldata);
> SELECT @.X850 AS myxml;'
> INSERT INTO @.mytable (myxml)
> execute @.rc = sp_executesql @.cmd
> (again, this works fine, until I try to limit privileges)
>

Tuesday, March 27, 2012

Executing Dynamic SQL with out Select Permission

Monday, March 19, 2012

Execute without Insert

Wednesday, March 7, 2012

Execute sp_start_job from stored procedure

Sunday, February 26, 2012

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost for nonadmin user after db migration with attach

Execute permission lost after database migration with attach

execute permission for stored procedures

Execute Permission for Database Role

Execute permission for all sprocs

EXECUTE permission deny

Execute Permission denied when running from IIS

Execute permission denied on stored procedure

Execute permission denied on stored procedure

Execute permission denied on 'sp_xml_preparedocument'

Friday, February 24, 2012

Execute permission denied on 'sp_xml_preparedocument'

Execute permission denied on 'sp_xml_preparedocument'

execute permission denied on schema?

execute permission denied on schema?

execute permission denied on schema?

EXECUTE AS question

Blog Archive

About Me