October 23, 2007 at 2:16 am
Here is the existing select statement I am trying to get working. Right now it only shows the personal account permissions, when it is supposed to show all users and there membership in the current database.
select sm.groupuid,su2.name as GroupName, su1.uid , su1.name as Users,
su1.roles as UserRole--,uo.OptionName as AppName,uo.OptionValue
from dbo.Sysusers su1
left join dbo.sysmembers sm
on su1.uid = sm.memberuid
left join dbo.Sysusers su2
on su2.uid = sm.groupuid
Thanks & Regards
Shashi kant chauhan
October 23, 2007 at 2:45 am
You can embed the above into a stored procedure and set that it executes in the context of dbo.
e.g.:
create proc a with execute as 'dbo' as begin
select ...
end
Regards,
Andras
October 24, 2007 at 5:51 pm
If you're using SQL Server 2005, you should get away from using the sysusers table and other system objects from SQL Server 2000 as they are deprecated. In some cases, they don't provide all the information you might need.
With respect to looking at the permissions, as indiciated you can use EXECUTE AS to change the context, however, the reason SQL Server 2005 restricts what users can see is to prevent a security issue. SQL Server 2000 allowed any user within a database to query all the permissions. This is an information disclosure issue. Unless the application absolutely requires it, this is something you should avoid doing.
K. Brian Kelley
@kbriankelley
November 2, 2007 at 8:03 am
>> If you're using SQL Server 2005, you should get away from using the sysusers table >>and other system objects from SQL Server 2000 as they are deprecated.
Since they are deprecated... Do they get replace by something else... I got the same type of problem on table sys.SYSPROCESSES?
I tried using
"CREATE PROCEDURE [dbo].[ ]
WITH EXECUTE AS 'dbo'" but Select on sys.SysProcesses always return an empty rowset...
Thanks...
November 2, 2007 at 8:21 am
>> I tried using "CREATE PROCEDURE [dbo].[ ]
WITH EXECUTE AS 'dbo'" but Select on sys.SysProcesses always return an empty rowset...
Sorry, for Select * from sys.SYSPROCESSES
the rowset is not empty... I can saw my process...
here is my Procedure:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SYS_show_All_Processes]
WITH EXECUTE AS 'dbo'
AS
BEGIN
Select * from sys.SYSPROCESSES
END
November 6, 2007 at 8:30 am
So I can ONLY see my process when I expect to see all the process...
So the Execute as 'dbo' don't work like it should... do you have an idea why?
Thanks
November 6, 2007 at 8:41 am
vbourdages (11/6/2007)
So I can ONLY see my process when I expect to see all the process...So the Execute as 'dbo' don't work like it should... do you have an idea why?
Thanks
You will need to set trustworthyness for your database (do read about it before setting it, it is a security risk!)
ALTER DATABASE mydatabase SET TRUSTWORTHY ON
Also, consider using the 2005 version of the sysprocesses: sys.dm_exec_sessions
Regards,
Andras
November 6, 2007 at 1:28 pm
Ok, Thanks, It worked with the command
" ALTER DATABASE mydatabase SET TRUSTWORTHY ON "
But I still have one little problem... This is only working when my user is 'dbo',
when I use my database user, I am getting this error:
Execute Master.dbo.SYS_show_All_Processes
Server: Msg 229, Level 14, State 5, Procedure SYS_show_All_Processes, Line 1
EXECUTE permission denied on object 'SYS_show_All_Processes', database 'master', schema 'dbo'.
Thank you for your help.
November 6, 2007 at 10:06 pm
vbourdages (11/6/2007)
when I use my database user, I am getting this error:Execute Master.dbo.SYS_show_All_Processes
Server: Msg 229, Level 14, State 5, Procedure SYS_show_All_Processes, Line 1
EXECUTE permission denied on object 'SYS_show_All_Processes', database 'master', schema 'dbo'.
The object exists in the master database. You'll have to grant explicit execute permissions on that object in the master database. By default, all logins which aren't a member of the sysadmin fixed server role (normal users, in other words) map into the database as guest. Guest doesn't have access to execute the stored procedure. So you'll either have to grant EXECUTE on the stored procedure to guest (consider this carefully from a security perspective before you do so), you will have to grant access to the master database for that particular login.
K. Brian Kelley
@kbriankelley
November 7, 2007 at 8:09 am
Thank you for your help everyone!
This was much appreciated!
Great Forum!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply