October 31, 2007 at 8:12 am
Hi, is access restricted for users who are only part of the Public server roles to the syscomments table/view? Not sure if its a table or view in SQL 2005.
I have a stored procedure that gets the object id using the object_id function then gets the Text from syscomments to analyze the sql text in that object.
Since we migrated to SQL 2005 this procedure does not work anymore. But if I execute the procedure with a login that is part of the sysadmin role then it works fine.
I've tried to change the procedure to EXECUTE AS 'sqllogin' where the SQL login name is part of the sysadmins. But when trying to execute the stored procedure then it returns with the following error:
Msg 15517, Level 16, State 1, Procedure PPP_ProcName, Line 0
Cannot execute as the database principal because the principal "sqllogin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
But when I log in with that user name then the stored procedure executes fine (without execute as)
Any other login on the public role returns no records. (no errors are also returned)
How can I work around this?
October 31, 2007 at 8:20 am
Some answers to the many questions :
- Have a look at the VIEW DEFINITION permission
- on 2005 use sys.sql_modules instead of syscomments (this does not chop up texts over a certain size)
- execute as requires a user, not a login (can you post the relevant part of the proc definition?)
Regards,
Andras
October 31, 2007 at 8:37 am
no code needed as sys.sql_modules did the trick 😛 . Thanks a lot.
Do EXECUTE AS only work for windows logins? I even tried DOMAIN\Administrator that has access to the database, but then the proc does not even compile, it says: "Cannot execute as user DOMAIN\Administrator, because it does not exist or you do not have permission"
The Administrator does have access to the database, I thought it would also automatically have admin rights to any database.
If i try EXECUTE AS Administrator then it compiles and runs but then I'm back to where I was where the stored procedure returned nothing. (also no errors)
So I'm not too clear as how to use the EXECUTE AS option. Can you shed some light on it for me please?
Thanks
October 31, 2007 at 8:41 am
Sorry, I lied. sys.sqlmodules did not work. I tested with an admin login
When using a login who is only part of the public role it returns NULL
here is the query that extracts the text:
Select definition FROM sys.sql_modules where object_ID = Object_id('PPP_ProcName')
October 31, 2007 at 8:42 am
Execute as expects a user in the database (items in the sys.users in the database). A simple example is:
create user blah without login
GO
create proc blah2 with execute as 'blah' as
begin
select user_name()
end
GO
exec blah2
it prints 'blah', which is the user the proc is executed as 🙂
In this case there is not even a login for this user.
Regards,
Andras
October 31, 2007 at 8:45 am
Johannes Fourie (10/31/2007)
Sorry, I lied. sys.sqlmodules did not work. I tested with an admin loginWhen using a login who is only part of the public role it returns NULL
here is the query that extracts the text:
Select definition FROM sys.sql_modules where object_ID = Object_id('PPP_ProcName')
You need the view definition permission:
create user blah without login
GO
create proc blah3 with execute as 'blah' as
begin
Select definition FROM sys.sql_modules where object_ID = Object_id('dbo.blah3')
end
GO
exec blah3
GO
grant view definition to blah
GO
exec blah3
The first execution will not return anything,
the second will get back the definition.
Andras
October 31, 2007 at 8:54 am
Johannes Fourie (10/31/2007)
Sorry, I lied. sys.sqlmodules did not work. I tested with an admin loginWhen using a login who is only part of the public role it returns NULL
here is the query that extracts the text:
Select definition FROM sys.sql_modules where object_ID = Object_id('PPP_ProcName')
One more note, try:
create proc procname with execute as 'dbo' as .....
Andras
October 31, 2007 at 8:56 am
Great!! Thanks, now it definitely works
October 31, 2007 at 9:10 am
Yes, using dbo also works
It may be a better choice, so no new users needs to be created?
Thanks again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply