none
SQL SERVER PERMISSIONS RRS feed

  • General discussion

  • Summary
    =============================
    This is a generic request for specific permissions on the Login/user.

    Requirement: Create login with the below Permissions

    1) Perform tasks to troubleshoot a problem:
    i.e. review SQL Agent jobs, run diagnostic system stored procs such as sp_who, sp_lock etc., check system tables etc..
    2) Has no access to user database objects i.e. cannot query or change user tables and user views, cannot execute user procedures etc..
    3) Can perform a backup but cannot perform a restore.
    4) Cannot create users or logins that might allow further elevated rights that would conflict with 1 and 2 above."

    All these requirements are interdependent.

    More Information
    =============================
    "A user / group that can do the following 4 things...

    1)Perform tasks to troubleshoot a problem:
    i.e. review SQL Agent jobs, run diagnostic system stored procs such as sp_who, sp_lock etc., check system tables etc..

    1) Create a login without the sysadmin role.
    2) We can query the system related information by granting the “Processadmin” role to the login. Deny the right "Alter Any Connection" from the securable "The Server" so that the user will not be able to kill the processes.

    * We cannot see the SQL error logs in SQL Management Studio when logged on as 'Support'

    The SQL server error logs can be viewed only if the login has sysadmin role. The work around for this could be to grant access for the user to view the error logs files from the default location.


    Agent Roles
    ==========

    SQL Server 2005 introduces the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm

    SQLAgentUserRole
    SQLAgentReaderRole
    SQLAgentOperatorRole

    We can grant the SQLAgentReaderRole so that the user would be able to review the SQL agent jobs

    deny execute rights on the stored procedures starting with sp_add so that the user will not be able to create the jobs


    Note: This is a new feature in the SQL SERVER 2005

    Requirement: A user who can view the jobs, but cannot execute or modify the jobs in SQL server 2000

    Grant “TargetServersRole”  database Role on msdb database.
    Goto Permission in the user properties and Deny exec access on the following objects

    1. All the stored procedures starting with “sp_add….”
    2. All the stored procedures starting with “sp_modify…”
    3. All the stored procedures starting with “sp_delete….”
    4. Sp_start_job
    5. Sp_stop_job

    This way, we can achieve the same task in SQL server 2000 as that of SQLAgentReaderRole in SQL server 2005


    2) Has no access to user database objects i.e. cannot query or change user tables and user views, cannot execute user procedures etc..

    Grant the following database roles to the user.

    db_denydatareader   : Denies permission to select data in the database.
    db_denydatawriter    : Denies permission to change data in the database.

    3) Can perform a backup but cannot perform a restore.

    We can grant “db_backupoperator”  database role to perform the backup operations.
    This user will not be able to restore the database in the same server as he wouldn’t have access to the disks. However, he can copy the file and restore the file in a different location.

    To restrict the user to read from the directory:

    We can grant only the write permission on the folder and remove the read and execute permissions.
    Right Click Folder> Properties > Security > Add the user> check write and uncheck read and execute.

    4) Cannot create users or logins that might allow further elevated rights that would conflict with 1 and 2 above.

    grant on  “ALTER ANY LOGIN” permission in the “Server” securable. The user shouldn’t be able to alter the other user. He would still be able to create the new login but couldn’t grant any rights. And moreover, this user will be able to view all the logins.

    In the login properties, go to securables> Click ADD> Select All Objects of the types > Select Logins > For each login we can deny (Alter, Control, Impersonate and view definition)  as per our requirement.


    Microsoft Online Community Support
    Wednesday, March 4, 2009 10:40 AM
    Moderator