EXECUTE AS question: permissions

Showing posts with label permissions. Show all posts

Thursday, March 29, 2012

executing sp

Hi
I have sp in which i run dbcc inputbuffer.
Can I, and how

grant permissions to somebody who
is not in sysadmin fixed server role to execute this sp?Hi,
No it is not possible.
DBCC INPUTBUFFER permissions default to members of the Sysadmin fixed server
role only, who can see any SPID. Other users can see any SPID they own.
Permissions are not transferable.
Thanks
Hari
MCDBA
<roman.ilic@.avtenta.si> wrote in message
news:efd1361.0403160208.7a8a3d9b@.posting.google.com...
> Hi
> I have sp in which i run dbcc inputbuffer.
> Can I, and how

grant permissions to somebody who
> is not in sysadmin fixed server role to execute this sp?sql

Sunday, February 26, 2012

Execute Permissions on 400 SPROCs

Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?

execute permissions aspnet sql server 2005

Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error

EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.

as far as im aware ive givne the ASPNET account the correct permissions

EXEC

sp_grantlogin [MachineName\ASPNET]

EXEC

sp_grantdbaccess [MachineName\ASPNET], [ASPNET]

** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.

Is there anything else I need to do ?

Try this:

GRANT EXEC on <storedproc> TO <machinename\user>

EXECUTE Permissions and Cross Database

I have an odd situation. Here are the details:
- I have three databases (A, B, C).
- I have a user that has EXECUTE and SELECT permissions on each
database.
- I have a stored procedure in A and B that does an update in C at one
point
- The stored procedure works fine from database A, but from database B,
it gives me the following UPDATE error: UPDATE permission denied on
object 'MyTable', database 'C', schema 'dbo'
- No dynamic SQL is used
- The database owners are the same as well as the table and stored
procedure owners
Can anyone help guide me on this?
Thanks,
MickyVerify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
for all databases involved.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||Yes, that is set at the server level to allow.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
> for all databases involved.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||Check the database property to allow cross database ownership chaining.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||It is set to false, but it is greyed out because of the server setting
I assume. They are set to compatability level 80 if that matters.
Also, they are all set to the same thing (chaining wise) which is what
stumps me (it works from Database A but not B)
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Check the database property to allow cross database ownership chaining.
>
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||It should be set to true. It can be set on the individual database level.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"MickyM" <javamick@.gmail.com> wrote in message
news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...
> It is set to false, but it is greyed out because of the server setting
> I assume. They are set to compatability level 80 if that matters.
> Also, they are all set to the same thing (chaining wise) which is what
> stumps me (it works from Database A but not B)
> Micky
> Arnie Rowland wrote:
>|||"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
>
Is the login executing the procedure the same in both cases? The login must
have access to database C.
David|||Yes, the login is the same in both cases.
Micky
David Browne wrote:
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>
> Is the login executing the procedure the same in both cases? The login mu
st
> have access to database C.
> David|||ok, this is resolved now. The bad part is I still don't have a clear
understanding of why the error was happening. I know it was related to
ownership chaining, but I don't know why. Here is what I did. I ran
this:
EXEC sys.sp_configure N'cross db ownership chaining', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
and then
EXEC sys.sp_configure N'cross db ownership chaining', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
So, basically I turned it off and then back on (at the server level).
Thanks for the help on this.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> It should be set to true. It can be set on the individual database level.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "MickyM" <javamick@.gmail.com> wrote in message
> news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...|||Hi
Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well
. You cann add them and grant any permissions that you want
"MickyM" <javamick@.gmail.com> wrote in message
news:1153160895.236692.9320@.75g2000cwc.googlegroups.com...
> Yes, the login is the same in both cases.
> Micky
> David Browne wrote:
>

EXECUTE permissions

I need to grant a user execute permissions on 1000 stored procedures. This
there any easy way to do this in SQL 2005?IF its 1000 procs among many procs, then you can do it the old fashioned way
.
execute this
select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects where
type = 'p' and <your other filters here>
copy paste the result set and execute the result set. Hope this helps|||I know the old way, but I was hoping they had made it easier in 2005.
"Omnibuzz" wrote:

> IF its 1000 procs among many procs, then you can do it the old fashioned w
ay.
> execute this
> select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects whe
re
> type = 'p' and <your other filters here>
> copy paste the result set and execute the result set. Hope this helps
>|||Do you have any particular way in mind that you are looking for.
Can you elaborate on the exact requirement.
Like doing it automatically and not have the manual intervention of copying
and pasting the result set. In that case you can use XP_EXECRESULTSET to do
it in one shot. it was available in SQL 2000 too.
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Andre" wrote:
> I know the old way, but I was hoping they had made it easier in 2005.
> "Omnibuzz" wrote:
>|||If you grouped them in the same schema, you can GRANT execute at the schema,
or even database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>I need to grant a user execute permissions on 1000 stored procedures. This
> there any easy way to do this in SQL 2005?|||So in AdventureWorks, how would I grant a user execute permissions on all th
e
HumanResource procedures with click on them individually? You said grant
permissions at the schema level, but I cant seem to make it work. If you
could provide step by step how to do this I would be grateful. Thanks.
"Tibor Karaszi" wrote:

> If you grouped them in the same schema, you can GRANT execute at the schem
a, or even database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>
>|||Right-click the schema, properties, permissions, browse the user...
Or execute below:
GRANT EXECUTE ON SCHEMA::schemaname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:1A9C0F71-9411-4FF5-8365-8C9D6C42B81A@.microsoft.com...
> So in AdventureWorks, how would I grant a user execute permissions on all
the
> HumanResource procedures with click on them individually? You said grant
> permissions at the schema level, but I cant seem to make it work. If you
> could provide step by step how to do this I would be grateful. Thanks.
> "Tibor Karaszi" wrote:
>

Friday, February 24, 2012

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/d...>
ols_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
Ron
Ron,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/de...tools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

What permissions need to be granted to developers to allow them to debug a
procedure in query analyzer.
Thanks
RonRon,
Try adding the user to the Master system database and then GRANT permissions
to execute the sp_sdidebug system stored procedure.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp
HTH
Jerry
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:DCC04B2B-AA46-41F2-813F-1E5DC1C16DF8@.microsoft.com...
> What permissions need to be granted to developers to allow them to debug a
> procedure in query analyzer.
> Thanks
> Ron

EXECUTE permission denied on object

I've taken over a project where the datasets are running stored
procedures on a database that I don't have permissions to execute
stored procedures.
I've changed the shared data source to a database I can use but I'm
getting the above message that still refers to the original database.
I don't know of anywhere else that I need to change.
Any ideas?andy
Grant EXECUTE permissions on the object. However , if a stopred procedure
uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
permissions on the undelaying tables as well
<andy.williams1971@.ntlworld.com> wrote in message
news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
> I've taken over a project where the datasets are running stored
> procedures on a database that I don't have permissions to execute
> stored procedures.
> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
> I don't know of anywhere else that I need to change.
> Any ideas?
>|||What do you mean grant EXECUTE permissions on the object? I am new to
Reporting services and have a very limited programming knowledge, and
unfortunately there is nobody on site who knows Reporting Services.
I work in an environment which I have full permissions to
execute/create/delete stored procedures. This environment is called
'Rules'. The initial database (to which I have no access, and am not
allowed access) is called 'Dev'. I've changed the datasource from Dev
to Rules but I'm getting the error message EXECUTE permissions denied
on object ........., database 'dev', owner 'dbo'
Uri Dimant wrote:
> andy
> Grant EXECUTE permissions on the object. However , if a stopred procedure
> uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
> permissions on the undelaying tables as well
>
>
> <andy.williams1971@.ntlworld.com> wrote in message
> news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
> > I've taken over a project where the datasets are running stored
> > procedures on a database that I don't have permissions to execute
> > stored procedures.
> >
> > I've changed the shared data source to a database I can use but I'm
> > getting the above message that still refers to the original database.
> > I don't know of anywhere else that I need to change.
> >
> > Any ideas?
> >|||Andy
Right click on the stored procedure and click Properties. At the left -top
click on Permission button and click Execute for specific user
<andy.williams1971@.ntlworld.com> wrote in message
news:1162978403.280403.69650@.e3g2000cwe.googlegroups.com...
> What do you mean grant EXECUTE permissions on the object? I am new to
> Reporting services and have a very limited programming knowledge, and
> unfortunately there is nobody on site who knows Reporting Services.
> I work in an environment which I have full permissions to
> execute/create/delete stored procedures. This environment is called
> 'Rules'. The initial database (to which I have no access, and am not
> allowed access) is called 'Dev'. I've changed the datasource from Dev
> to Rules but I'm getting the error message EXECUTE permissions denied
> on object ........., database 'dev', owner 'dbo'
>
> Uri Dimant wrote:
>> andy
>> Grant EXECUTE permissions on the object. However , if a stopred procedure
>> uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
>> permissions on the undelaying tables as well
>>
>>
>> <andy.williams1971@.ntlworld.com> wrote in message
>> news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
>> > I've taken over a project where the datasets are running stored
>> > procedures on a database that I don't have permissions to execute
>> > stored procedures.
>> >
>> > I've changed the shared data source to a database I can use but I'm
>> > getting the above message that still refers to the original database.
>> > I don't know of anywhere else that I need to change.
>> >
>> > Any ideas?
>> >
>|||I'm sorry for being useless, but I've looked on the data tab and I
cannot get the properties window for the stored procedure
Uri Dimant wrote:
> Andy
> Right click on the stored procedure and click Properties. At the left -top
> click on Permission button and click Execute for specific user
>
> <andy.williams1971@.ntlworld.com> wrote in message
> news:1162978403.280403.69650@.e3g2000cwe.googlegroups.com...
> > What do you mean grant EXECUTE permissions on the object? I am new to
> > Reporting services and have a very limited programming knowledge, and
> > unfortunately there is nobody on site who knows Reporting Services.
> >
> > I work in an environment which I have full permissions to
> > execute/create/delete stored procedures. This environment is called
> > 'Rules'. The initial database (to which I have no access, and am not
> > allowed access) is called 'Dev'. I've changed the datasource from Dev
> > to Rules but I'm getting the error message EXECUTE permissions denied
> > on object ........., database 'dev', owner 'dbo'
> >
> >
> >
> > Uri Dimant wrote:
> >> andy
> >> Grant EXECUTE permissions on the object. However , if a stopred procedure
> >> uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
> >> permissions on the undelaying tables as well
> >>
> >>
> >>
> >>
> >> <andy.williams1971@.ntlworld.com> wrote in message
> >> news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
> >> > I've taken over a project where the datasets are running stored
> >> > procedures on a database that I don't have permissions to execute
> >> > stored procedures.
> >> >
> >> > I've changed the shared data source to a database I can use but I'm
> >> > getting the above message that still refers to the original database.
> >> > I don't know of anywhere else that I need to change.
> >> >
> >> > Any ideas?
> >> >
> >|||I meant you have Enterprise Manager Tool installed on the computer.
<andy.williams1971@.ntlworld.com> wrote in message
news:1162981702.059182.305600@.k70g2000cwa.googlegroups.com...
> I'm sorry for being useless, but I've looked on the data tab and I
> cannot get the properties window for the stored procedure
> Uri Dimant wrote:
>> Andy
>> Right click on the stored procedure and click Properties. At the
>> left -top
>> click on Permission button and click Execute for specific user
>>
>> <andy.williams1971@.ntlworld.com> wrote in message
>> news:1162978403.280403.69650@.e3g2000cwe.googlegroups.com...
>> > What do you mean grant EXECUTE permissions on the object? I am new to
>> > Reporting services and have a very limited programming knowledge, and
>> > unfortunately there is nobody on site who knows Reporting Services.
>> >
>> > I work in an environment which I have full permissions to
>> > execute/create/delete stored procedures. This environment is called
>> > 'Rules'. The initial database (to which I have no access, and am not
>> > allowed access) is called 'Dev'. I've changed the datasource from Dev
>> > to Rules but I'm getting the error message EXECUTE permissions denied
>> > on object ........., database 'dev', owner 'dbo'
>> >
>> >
>> >
>> > Uri Dimant wrote:
>> >> andy
>> >> Grant EXECUTE permissions on the object. However , if a stopred
>> >> procedure
>> >> uses dynamic sql within , you'll have to grant SELECT/UPDATE/DELETE
>> >> permissions on the undelaying tables as well
>> >>
>> >>
>> >>
>> >>
>> >> <andy.williams1971@.ntlworld.com> wrote in message
>> >> news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
>> >> > I've taken over a project where the datasets are running stored
>> >> > procedures on a database that I don't have permissions to execute
>> >> > stored procedures.
>> >> >
>> >> > I've changed the shared data source to a database I can use but I'm
>> >> > getting the above message that still refers to the original
>> >> > database.
>> >> > I don't know of anywhere else that I need to change.
>> >> >
>> >> > Any ideas?
>> >> >
>> >
>|||> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
Are you running the report through the VS IDE or after deployment? If you
change the data source and run the report from the IDE, the new database
should be used and reflected in any error messages. However, when you
deploy a report, existing data sources on the report server are not
overwritten unless you specify 'true' for the data source overwrite
property.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<andy.williams1971@.ntlworld.com> wrote in message
news:1162977266.829057.169620@.b28g2000cwb.googlegroups.com...
> I've taken over a project where the datasets are running stored
> procedures on a database that I don't have permissions to execute
> stored procedures.
> I've changed the shared data source to a database I can use but I'm
> getting the above message that still refers to the original database.
> I don't know of anywhere else that I need to change.
> Any ideas?
>

Wednesday, February 15, 2012

EXECUTE AS question

I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
see all sessions, without granting them additional permissions. I
thought I could do something like this:
CREATE PROCEDURE [dbo].[sp_who2_all]
WITH EXECUTE AS SELF
AS
EXEC sp_who2
The procedure creates successfully, and it does execute under a
different security context - I can see this because the login column =
sa. However, it only returns one record, for the current session only
(as it would for a user without the VIEW SERVER STATE permission). Of
course, if I connect as sa and run sp_who2, I see information from all
sessions.
Why is it behaving this way, and is there a good workaround?
Thanks - stavros
On Mar 8, 7:28 am, stavros <stav...@.mailinator.com> wrote:
> I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
> see all sessions, without granting them additional permissions. I
> thought I could do something like this:
> CREATE PROCEDURE [dbo].[sp_who2_all]
> WITH EXECUTE AS SELF
> AS
> EXEC sp_who2
> The procedure creates successfully, and it does execute under a
> different security context - I can see this because the login column =
> sa. However, it only returns one record, for the current session only
> (as it would for a user without the VIEW SERVER STATE permission). Of
> course, if I connect as sa and run sp_who2, I see information from all
> sessions.
> Why is it behaving this way, and is there a good workaround?
> Thanks - stavros
sp_who requires VIEW SERVER STATE permission on the server to see all
executing sessions on the instance of SQL Server. Otherwise, the user
sees only the current session.
Please refer http://msdn2.microsoft.com/en-us/library/ms174313.aspx
and http://msdn2.microsoft.com/en-us/library/ms186717.aspx.
|||On Mar 8, 6:54 am, DDD <1983...@.gmail.com> wrote:
> On Mar 8, 7:28 am, stavros <stav...@.mailinator.com> wrote:
>
>
>
>
> sp_who requires VIEW SERVER STATE permission on the server to see all
> executing sessions on the instance of SQL Server. Otherwise, the user
> sees only the current session.
> Please referhttp://msdn2.microsoft.com/en-us/library/ms174313.aspx
> andhttp://msdn2.microsoft.com/en-us/library/ms186717.aspx.
Agreed - sorry if I wasn't clear, but that's the whole point. My
users don't have VIEW SERVER STATE permission, so I'm trying to write
a SP which will EXECUTE AS a user who does have that permission (sa).
If I log in as sa, sp_who2 returns all sessions (as expected). The SP
I described does in fact execute as sa (as expected). However, it
still only lists the current session, as if it's executing without the
VIEW SERVER STATE perm. That's the piece I don't understand, and what
I need a solution for.
|||On Mar 7, 4:28 pm, stavros <stav...@.mailinator.com> wrote:
> I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
> see all sessions, without granting them additional permissions. I
> thought I could do something like this:
> CREATE PROCEDURE [dbo].[sp_who2_all]
> WITH EXECUTE AS SELF
> AS
> EXEC sp_who2
> The procedure creates successfully, and it does execute under a
> different security context - I can see this because the login column =
> sa. However, it only returns one record, for the current session only
> (as it would for a user without the VIEW SERVER STATE permission). Of
> course, if I connect as sa and run sp_who2, I see information from all
> sessions.
> Why is it behaving this way, and is there a good workaround?
> Thanks - stavros
Can someone try this same exercise and see if they get the same
results? I still can't figure out this behavior, so maybe it's
something particular to my environment. Thanks - Stavros

EXECUTE AS question

I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
see all sessions, without granting them additional permissions. I
thought I could do something like this:
CREATE PROCEDURE [dbo].[sp_who2_all]
WITH EXECUTE AS SELF
AS
EXEC sp_who2
The procedure creates successfully, and it does execute under a
different security context - I can see this because the login column = sa. However, it only returns one record, for the current session only
(as it would for a user without the VIEW SERVER STATE permission). Of
course, if I connect as sa and run sp_who2, I see information from all
sessions.
Why is it behaving this way, and is there a good workaround?
Thanks - stavrosOn Mar 8, 7:28 am, stavros <stav...@.mailinator.com> wrote:
> I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
> see all sessions, without granting them additional permissions. I
> thought I could do something like this:
> CREATE PROCEDURE [dbo].[sp_who2_all]
> WITH EXECUTE AS SELF
> AS
> EXEC sp_who2
> The procedure creates successfully, and it does execute under a
> different security context - I can see this because the login column => sa. However, it only returns one record, for the current session only
> (as it would for a user without the VIEW SERVER STATE permission). Of
> course, if I connect as sa and run sp_who2, I see information from all
> sessions.
> Why is it behaving this way, and is there a good workaround?
> Thanks - stavros
sp_who requires VIEW SERVER STATE permission on the server to see all
executing sessions on the instance of SQL Server. Otherwise, the user
sees only the current session.
Please refer http://msdn2.microsoft.com/en-us/library/ms174313.aspx
and http://msdn2.microsoft.com/en-us/library/ms186717.aspx.|||On Mar 8, 6:54 am, DDD <1983...@.gmail.com> wrote:
> On Mar 8, 7:28 am, stavros <stav...@.mailinator.com> wrote:
>
> > I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
> > see all sessions, without granting them additional permissions. I
> > thought I could do something like this:
> > CREATE PROCEDURE [dbo].[sp_who2_all]
> > WITH EXECUTE AS SELF
> > AS
> > EXEC sp_who2
> > The procedure creates successfully, and it does execute under a
> > different security context - I can see this because the login column => > sa. However, it only returns one record, for the current session only
> > (as it would for a user without the VIEW SERVER STATE permission). Of
> > course, if I connect as sa and run sp_who2, I see information from all
> > sessions.
> > Why is it behaving this way, and is there a good workaround?
> > Thanks - stavros
> sp_who requires VIEW SERVER STATE permission on the server to see all
> executing sessions on the instance of SQL Server. Otherwise, the user
> sees only the current session.
> Please referhttp://msdn2.microsoft.com/en-us/library/ms174313.aspx
> andhttp://msdn2.microsoft.com/en-us/library/ms186717.aspx.
Agreed - sorry if I wasn't clear, but that's the whole point. My
users don't have VIEW SERVER STATE permission, so I'm trying to write
a SP which will EXECUTE AS a user who does have that permission (sa).
If I log in as sa, sp_who2 returns all sessions (as expected). The SP
I described does in fact execute as sa (as expected). However, it
still only lists the current session, as if it's executing without the
VIEW SERVER STATE perm. That's the piece I don't understand, and what
I need a solution for.|||On Mar 7, 4:28 pm, stavros <stav...@.mailinator.com> wrote:
> I'm trying to give my users the ability to run sp_who2 on SQL 2005 and
> see all sessions, without granting them additional permissions. I
> thought I could do something like this:
> CREATE PROCEDURE [dbo].[sp_who2_all]
> WITH EXECUTE AS SELF
> AS
> EXEC sp_who2
> The procedure creates successfully, and it does execute under a
> different security context - I can see this because the login column => sa. However, it only returns one record, for the current session only
> (as it would for a user without the VIEW SERVER STATE permission). Of
> course, if I connect as sa and run sp_who2, I see information from all
> sessions.
> Why is it behaving this way, and is there a good workaround?
> Thanks - stavros
Can someone try this same exercise and see if they get the same
results? I still can't figure out this behavior, so maybe it's
something particular to my environment. Thanks - Stavros

Thursday, March 29, 2012

executing sp

Sunday, February 26, 2012

Execute Permissions on 400 SPROCs

execute permissions aspnet sql server 2005

EXECUTE Permissions and Cross Database

EXECUTE permissions

Friday, February 24, 2012

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

EXECUTE permission denied on object 'sp_sdidebug', database 'maste

EXECUTE permission denied on object

EXECUTE permission denied on object

EXECUTE permission denied on object

Wednesday, February 15, 2012

EXECUTE AS question

EXECUTE AS question

EXECUTE AS question

Blog Archive

About Me