June 18, 2010 at 3:08 am
I have a login that has Read and write permissions on user databases, but I would also like this user to be able to view the SQL Error logs.
I know that securityadmin will grant this option to the login, but it unfortunately comes with other permissions like 'manage logins' and 'CREATE DATABASE permissions' - which I don't want this user to have.
Any ideas on how I can grant only the viewing of the error logs?
Thanks,
June 22, 2010 at 1:15 pm
This can be done by granting execute permissions on the undocumented system stored procedure xp_readerrorlog.
Two caveats are: 1) undocumented stored procedures can change in future editions of SQL Server and 2) by allowing an application user to read the error log, you are creating a security vulnerability.
As you probably know, the error log shows very specific information about the SQL Server instance, the database names, the version, and so forth.
I would recommend reading this article:
http://www.sommarskog.se/grantperm.html.
He discusses impersonation and the various security risks involved. In fact, the recommends against the method that I used to make this work.
Anyway, the script is below. I wrote this in SQL Server 2008 Express, but I think it should work in 2005.
USE [master]
GO
-- Drop the user if it exists
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ErrorLogReader')
DROP USER [ErrorLogReader]
GO
-- Drop the login if it exists
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ErrorLogReader')
DROP LOGIN [ErrorLogReader]
GO
-- Create a login who will get permission to read the error log
CREATE LOGIN ErrorLogReader WITH PASSWORD = 'C0mpl3xPa$$w@rd**'
GO
-- Deny connection ability to this login for security
DENY CONNECT SQL TO [ErrorLogReader]
GO
-- Create a user with this login
CREATE USER ErrorLogReader FOR LOGIN ErrorLogReader
GO
-- Grant execute permission to the undocumented stored procedure xp_readerrorlog
GRANT EXECUTE ON xp_readerrorlog TO ErrorLogReader
GO
-- Create a database to test this in
CREATE DATABASE ErrorLogDB
GO
-- Switch to the new database
USE [ErrorLogDB]
GO
-- Create the ErrorLogReader user here as well
CREATE USER ErrorLogReader FOR LOGIN ErrorLogReader
GO
-- Create a stored procedure which will execute xp_readerrorlog
CREATE PROCEDURE usp_readerrorlog
WITH EXECUTE AS 'ErrorLogReader'
AS
BEGIN
EXEC xp_readerrorlog
END
GO
-- Create a special user to test permissions
CREATE USER TestUser WITHOUT LOGIN
GO
-- Grant the TestUser execute permissions on the stored procedure
GRANT EXECUTE ON usp_readerrorlog TO TestUser
GO
-- Test running under the test user's context
EXECUTE AS USER = 'TestUser'
-- Try executing xp_errorlog
EXEC xp_readerrorlog
-- You should get an error message like this:
/*
Msg 229, Level 14, State 5, Procedure xp_readerrorlog, Line 1
The EXECUTE permission was denied on the object 'xp_readerrorlog', database 'mssqlsystemresource', schema 'sys'.
*/
-- Try executing the stored procedure
EXEC usp_readerrorlog
-- Revert back to original context
REVERT
-- Switch back to master
USE [master]
GO
-- Clean up everything we created
DROP DATABASE [ErrorLogDB]
GO
-- Drop the ErrorLogUser user from the master db
DROP USER ErrorLogReader
GO
-- Drop the ErrorLogUser login from the master db
DROP LOGIN ErrorLogReader
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply