Recently we had a group of DBA who have limited access on an instance. They wanted to view the SQL logs but were unable. I found it difficult to find a clear solution for this, but after some trial and error here is what I came up with:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO -- Create a user on the master database to grant xp_readerrorlog permissions CREATE USER _USERNAME_or_GROUPNAME FOR LOGIN _USERNAME_or_GROUPNAME GO -- Grant execute permission to stored procedure xp_readerrorlog GRANT EXECUTE ON xp_readerrorlog TO _USERNAME_or_GROUPNAME GO |
Even after this you might get a message: The user does not have permissions to perform this action Error: 297.
In order to correct this you must grant VIEW SERVER STATE to the login:
|
1 2 3 4 |
USE [master] GO GRANT VIEW SERVER STATE TO _USERNAME_or_GROUPNAME GO |
Now the user or group should have access to view the SQL error logs.