November 2, 2009 at 5:33 am
Hi,
we want to enable our users to query some information about their databases which usually can only admins. The plan is to provide some functions/procedures, and let them be run as a priveleged "Admin" User.
So, we have an admin_login on the server and an admin_user on the database mapped to the admin_login.
The following Function only works if it is run as the admin_login:
CREATE FUNCTION [admin].[fn_db_get_sqltext]
(
@spid SMALLINT
)
RETURNS NVARCHAR(4000)
--
-- admin.fn_db_get_sqltext
--
-- Retrieves the SQL text for the given sessions id, only for the current
-- database.
--
-- Paremeters:
-- @spid The session id the retrieve the current SQL text for.
--
-- Return value:
-- The SQL text for the given session_id.
--
BEGIN
DECLARE @SqlHandle VARBINARY(64)
DECLARE @SqlText NVARCHAR(4000)
-- Get sql_handle for the given spid.
SELECT @SqlHandle = sql_handle FROM sys.dm_exec_requests WITH (nolock)
WHERE database_id = DB_ID()
AND session_id = @spid
-- Get the SQL text for the given sql_handle.
SELECT @SqlText = [text] FROM sys.dm_exec_sql_text(@SqlHandle)
RETURN @SqlText
END
This works:
execute as LOGIN = 'xxx_admin'
select [admin].[fn_db_get_sqltext] (1)
--exec [admin].[sp_list_blocking_sessions]
revert
But I have to get it to work with the Admin_USER on the Users database. The execute as clause in the function works with a user, not with a login.
So, what can do?
Thanks in advance,
Tobe
November 2, 2009 at 5:39 am
execute as LOGIN = 'xxx_admin'
select [admin].[fn_db_get_sqltext] (1)
--exec [admin].[sp_list_blocking_sessions]
revert
does this work
execute as USER = 'Admin_user'
select [admin].[fn_db_get_sqltext] (1)
--exec [admin].[sp_list_blocking_sessions]
revert
You can specify a user name for use of a function, let me know if you have any problems
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 2, 2009 at 5:47 am
No!
Msg 297, Level 16, State 1, Line 4
The user does not have permission to perform this action.
I had added the folliwing permissions to the login:
use master
grant view server state to xxx_Admin
GO
sp_addsrvrolemember 'xxx_Admin', 'sysadmin'
November 2, 2009 at 5:50 am
tobe_ha (11/2/2009)
No!
Msg 297, Level 16, State 1, Line 4
The user does not have permission to perform this action.
I had added the folliwing permissions to the login:
use master
grant view server state to xxx_Admin
GO
sp_addsrvrolemember 'xxx_Admin', 'sysadmin'
Let me have a look, bear in mind that the login will need the rights to all objects referenced within the function. and if you are sysadmin, you already have full rights, you dont need to grant anything.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 2, 2009 at 6:19 am
But I still have the problem:
as login works:
execute as LOGIN = 'xxx_admin'
select [admin].[fn_db_get_sqltext] (1)
revert
but as USER works not.
execute as USER = 'xxx_admin'
select [admin].[fn_db_get_sqltext] (1)
revert
November 2, 2009 at 6:46 am
Dont really have the time for this.
the problem that I see is that, in the function the second select is referencing sys.dm_exec_sql_text, which needs master database access and also requires view server state, which you grant at the login level, not the user level.
I have replicated your entire setup and I cannot get it to work in the spare time I had. I am pretty sure there are easier ways of giving this access.
Over to you guys, I need to get releases done.
Just out of curiosity, in which database would the function be stored in. would it be master (not recommended), or one of the user databases?
the reason it probably works as a login, is that that login has a user/access in/to the master database, which it wont have as when running it as a user.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 12, 2009 at 12:38 am
Just out of curiosity, in which database would the function be stored in. would it be master (not recommended), or one of the user databases?
the reason it probably works as a login, is that that login has a user/access in/to the master database, which it wont have as when running it as a user.
Hi,
the function should be installed in the user database, as the admins surely doesn't want our project to change the master database (there are other projects on the same server)
The problem is still unsolved. Are there ideas? If it is not possible, please say so.
Tobe
July 20, 2011 at 5:08 pm
bump ^^
I am eagerly looking for some kind of resolution to this as well. in my case as login doesn't work either. I need to delegate access to view some dmvs and cannot give sysadmin priviliges.
July 21, 2011 at 10:10 am
Rather than jumping through all the hoops of impersonating a SYSADMIN, you could just grant the login(s) VIEW SERVER STATE permission.
http://msdn.microsoft.com/en-us/library/ms186717.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply