Results 1 to 6 of 6

Thread: Sysadmin role

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Sysadmin role

    Hi All,

    Is it possible to give a user a sysadmin role and then deny some of the privileges?

    I am a junior dba, I should be able to view only everything that the sysadmin can see, i.e. db properties, logins, packages, jobs etc.

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Any particular class of privileges?

    You could maybe use Revoke (see BOL).

  3. #3
    Join Date
    Jul 2004
    Posts
    268

    Sysadmin role

    I looked at revoke and it is not exactly what I am looking for. I shouldn't be able to modify the jobs, take db offline, or detach it, or restore it. And these are just few examples.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How about we go at this from the other side. What do you have to do? View only implies the guest users in each database should be able to handle your needs.

  5. #5
    Join Date
    Jul 2004
    Posts
    268

    Sysadmin role

    I need to create a user that would be able to view everything within Enterprise Manager but not update or change anything.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Create a login with no server roles associated with it, and don't add him to any databases. Then run the following in each user database (i.e. not master or msdb):
    Code:
    sp_adduser guest
    This should allow you to view anything but data in the tables. Oddly enough, going further than that can lead to some security holes, and already has opened up a few I would rather not think of.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •