View explicit permissions given under Securables

[williey]

How can I see what objects permissions are given to a specific user through the Management Studio?

I want to get able to see what kinds of permissions does a user has on tables, views, functions or stored procedures.

Will

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[ptheriault]

Here are a couple of scipts that can help you determine this.

This script will show you what role each user is assigned to for each db.

sp_msforeachdb 'use ?
if exists (select 1
        from sysmembers
        where user_name(memberuid) not in (''dbo'',''guest'')
        and user_name(groupuid) in (''db_owner'',''db_datareader'',''db_datawriter''))
    begin
        select cast(db_name() as varchar(30)) as [Database Name]
    
        select case when grouping(groupuid) = 0 and grouping(memberuid) = 1
                then cast(user_name(groupuid) as varchar(30))
                else ''''
                end as [Role],
            case when grouping(groupuid) = 0 and grouping(memberuid) = 0
                then cast (user_name(memberuid) as varchar(40))
                else ''''
                end as [member]
        from dbo.sysmembers m
        where user_name(memberuid) not in (''dbo'',''guest'')
        and (user_name(groupuid) like ''db_%'')
                  or exists (select 1 from sysmembers
                where memberuid = m.groupuid
                and user_name(groupuid) like ''db_%'')
                group by groupuid, memberuid
                with rollup
        order by groupuid, memberuid, grouping(groupuid) desc, grouping(memberuid)
    end'

This will show permissions.

CREATE PROCEDURE spshowperms  
@username     sysname = NULL
as
/* Show table permission for database user  RES -- 2006-06-19 */

select distinct a.name as username,c.name as tablename,e.name as action
from sysusers a,syspermissions b,sysobjects c,sysprotects d, master.dbo.spt_values e
where a.uid=b.grantee
  and a.name = @username
  and b.id=c.id
  and c.id=d.id
  and a.uid=d.uid
  and d.action = e.number
  and e.type = 'T'
order by a.name,c.name,e.name

GO


declare @username varchar(255)
select @username = 'LoginWithAProblem'
select distinct e.name as action,c.name as tablename,a.name as username
into #permissionTable
from sysusers a,syspermissions b,sysobjects c,sysprotects d,
master.dbo.spt_values e
where a.uid=b.grantee
  and a.name = @username
  and b.id=c.id
  and c.id=d.id
  and a.uid=d.uid
  and d.action = e.number
  and e.type = 'T'
order by a.name,c.name,e.name 


select 'Grant ' + [action] + ' on ' + tablename + ' to ' + username from
#permissionTable
--Run these results to set permissions for tables and views.

exec spshowperms

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Hereis a much easier script for checking out permissions on a SQL 2005 database.

select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
	sys.database_permissions.permission_name, 
	sys.database_permissions.state permission_state,
	sys.database_permissions.state_desc,
	state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = 
	sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = 
	sys.database_principals.principal_id
order by 1, 2, 3, 5

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[williey]

Thanks Denny, the results of your query is pretty close to what I was looking for.

Its amazing that these information is not available through Studio Management.

Will

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[mrdenny]

Yeah, it's annoying that there isn't an easy way so see the rights that each user has.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com