May 31, 2012 at 4:00 am
Hi,
I need a user to have access to 'SQL Server Logs'. For that this user have to be server securityadmin role member. So this user will have the next permission:
ServerRole Permission
-----------------------------------------------------
securityadminAdd member to securityadmin
securityadminGrant/deny/revoke CREATE DATABASE
securityadminRead the error log
securityadminsp_addlinkedsrvlogin
securityadminsp_addlogin
securityadminsp_defaultdb
securityadminsp_defaultlanguage
securityadminsp_denylogin
securityadminsp_droplinkedsrvlogin
securityadminsp_droplogin
securityadminsp_dropremotelogin
securityadminsp_grantlogin
securityadminsp_helplogins
securityadminsp_password
securityadminsp_remoteoption (update)
securityadminsp_revokelogin
I only need 'Read the error log' for my user. How i can deny the others permission to my user?
Many thanks.
May 31, 2012 at 4:12 am
why not just create a proc, which executes sp_readerrorlog as a different user, which has access to read the logs, then just grant your users the rights they need to execute the proc, that way you dont have to worry about inheritance as the only thing they can do is read the error log via the proc, just ensure you create the logreader account with a stupid password which no one will remember and store it securly increase you need to re-create it for some reason.
eg
create login logreader
grant access to serveradmin to logreader
create proc readerrorlog
execute as login 'logreader'
as
sp_readerrorlog
grant execute on readerrorlog to user1
May 31, 2012 at 4:41 am
Hi Anthony,
Thanks for you reply but this is not valid solution for me. The user have to have permission for open sql server logs from SSMS. For that, as i comment in the previos post, the user have to bee server security admin role member. But with this solution the user have more permission that really he need and i need to deny/revoke the permission that he don't need (for security reason).
Similar problem i have whith 'Maintenance Plain' folder; if i add the user to sysadmin server role the user can see the Maintenance Plain Folder -that exist under 'Management' folder (this method is the unique that run for me; i don't know if exist another best method); but obviously the user have too many permission and i would like to revoke the permission that the user don't need.
Any other idea? How i can revoke permission from permission inherited from server role or exist any other way to see the 'sql server logs' and maintenance plain folder from SSMS without add a user to server role?
Many thanks in advance.
May 31, 2012 at 4:52 am
Well you will need to find the level of rights which securityadmin gives the user then use the DENY keywords to manually deny the permissions you dont want them to have.
As for granting people sysadmin rights, if you do this and you deny them access to do something, they can still do it as if you have SA rights you are God and nothing can stop you, not even explicite restrictions, but as you need SA rights to create and manage maintenance plans your stuck with letting people have full control over your servers
So securityadmin you can revoke what you dont want them to have, SysAdmin you cant. Permissions are cumulative and deny takes precident over grant, but not when your sysadmin.
May 31, 2012 at 5:02 am
Ok.
Many thanks for you help Anthony.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply