Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    101

    Unanswered: user permission list

    Hi, Does anyone has a script to get user permissions on all tables?
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Transact-SQL can do this, but SQL-DMO is the approved method. I don't have a "ready to use" script, but they aren't hard to create.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not so sure it is easy to create. At least, a script to find all permissions of a user both denies and grants, and any permissions inherited from various role memberships. If you just look for permissions applied to a user, then it is relatively easy.

  4. #4
    Join Date
    Feb 2003
    Posts
    19
    I’ve set the permission on enterprise and transact-SQL, but SQL-DMO refuse the process... :-)

    cheers
    Sjaiful Bahri

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by sbahri
    I’ve set the permission on enterprise and transact-SQL, but SQL-DMO refuse the process...
    I don't understand what you are trying to say. Please try again, with a bit more detail.

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, if this were DB2, you'd go to the system cat table SYSIBM.SYSTABAUTH....

    Nothing like that in SQL Server?

    Hold on, I'll go look at the catalog
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This is about what you would need. I have not experimented with DENY, so I don't have any of those codes. This also does not take into consideration any implied permissions like those that would be given by membership in db_owner or db_datareader. Getting those could be considerably more complex.

    Code:
    declare @name varchar(120)
    set @name = 'guest'
    
    
    select case when p.action = 26 then 'References'
    	  when p.action = 193 then 'Select'
    	  when p.action = 195 then 'Insert'
    	  when p.action = 196 then 'Delete'
    	  when p.action = 197 then 'Update'
    	  when p.action = 224 then 'Execute'
    	  else 'something else' end as permission, 
    	case when p.protecttype = 206 then 'DENIED'
    	  when p.protecttype = 205 then 'Granted'
    	  when p.protecttype = 204 then 'Granted with grant option'
    	  else 'Something else' end as "deny/grant",
    	o.name as "Object Name",
    	case when a.uid > 16000 then 'Inheirited' 
    	  else 'Direct' end as Source
    from sysprotects p join
    	sysobjects o on p.id = o.id join
    	(select u.uid
    	from sysusers u
    	where u.name = @name
    	union all
    	select r.uid
    	from sysusers u join
    		sysmembers m on u.uid = m.memberuid join
    		sysusers r on m.groupuid = r.uid
    	where u.name = @name) a on p.uid = a.uid
    EDIT: Fixed a bug where users who are not part of at least one role do not show. Also added deny/grant. Shoulda read BOL....
    Last edited by MCrowley; 05-01-06 at 12:59.

  8. #8
    Join Date
    Feb 2003
    Posts
    19
    Quote Originally Posted by Pat Phelan
    I don't understand what you are trying to say. Please try again, with a bit more detail.

    -PatP
    I mean maybe out of topic, but I’ve experience when I set permission using enterprise but SQL – DMO refused the permission.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by sbahri
    I mean maybe out of topic, but I’ve experience when I set permission using enterprise but SQL – DMO refused the permission.
    I still don't think that I understand what you mean. Do you mean that you granted permissions using SQL Enterprise Manager, but that you couldn't script those permissions back out using SQL-DMO? That's what I think you meant, but I don't think that is possible.

    From a whole table perspective (meaning you don't have any column level permissions), I'd suggest using:
    Code:
    SELECT v.name + ' ' + a.name + ' ON [' 
    +  u1.name + '].[' + o.name + '] TO [' + u2.name + ']'
       FROM dbo.sysprotects AS p
       JOIN dbo.sysobjects AS o
          ON (o.id = p.id)
       JOIN dbo.sysusers AS u1
          ON (u1.uid = o.uid)
       JOIN dbo.sysusers AS u2
          ON (u2.uid = p.uid)
       JOIN master.dbo.spt_values AS a
          ON ('T' = a.type
          AND a.number = p.action)
       JOIN master.dbo.spt_values AS v
          ON ('T' = v.type
          AND v.number = p.protecttype)
    -PatP

Posting Permissions

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