Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: How to list permissions for Public Role for a database in SQL Server

    Hello,
    Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?

    Also looking for the SQL to find the permissions granted to the user Guest in a database ?


    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can do both of them using the following SELECT statement:
    Code:
    SELECT a.[name] + ' ' + v.[name] + ' ON ' + QuoteName(oo.[name]) 
    + '.' + QuoteName(o.[name]) + ' TO ' + QuoteName(u.[name])
       FROM dbo.sysprotects AS p
       JOIN master.dbo.spt_values AS a
          ON (a.number = p.protecttype
          AND 'T' = a.type)
       JOIN master.dbo.spt_values AS v
          ON (v.number = p.action
          AND 'T' = v.type)
       JOIN dbo.sysobjects AS o
          ON (o.id = p.id)
       JOIN dbo.sysusers AS oo
          ON (oo.uid = o.uid)
       JOIN dbo.sysusers AS u
          ON (u.uid = p.uid)
       WHERE  'public' = u.name
    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    93
    Pat Phelan,
    Thanks for this. It works perfectely for Public Role.

    However how will it work for the Guest user ?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mgupta
    However how will it work for the Guest user ?
    Yes, if you change the constant it will work for any user.

    -PatP

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    You can also use:

    sp_helprotect @username='public'


    sp_helprotect @username='guest'


    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Oct 2003
    Posts
    93
    thanks hmscott and PatP for the solutions.

Posting Permissions

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