Role permissions

try sp_helprotect

or sp_helprole
sp_helpuser

How about sp_helprole and sp_helprolemember?

Thanks I'll check these when I get back to work in an hour.
Do these iterate through all the user defined roles or do you have to specify which role as a parameter?
I'd like to be able to loop through them all if possible rather than run it once for every role (there are quite a few!) in the DB.

You can refer BOL for details
1.sp_helprolemember
   Returns information about the members of a role in the current database.

Syntax
sp_helprolemember [ [ @rolename = ] 'role' ]

Arguments
[@rolename =] 'role'

Is the name of a role in the current database. role is sysname, with a default of NULL. role must exist in the current database. If role is not specified, then all roles that contain at least one member from the current database are returned.


Use sp_helpsrvrolemember to display the members of a fixed server role.

2. sp_helprole
Returns information about the roles in the current database.

Syntax
sp_helprole [ [ @rolename = ] 'role' ]

To view the permissions associated with the role, use sp_helprotect.
To view the members of a database role, use sp_helprolemember.

thanks - I'm trying to wrap this up and pick my columns so that I can chuck it into a spreadsheet or repoirting services report.

show me all user roles (group by role)
show me all members of this role.
show me the permissions that this role has (on tables)

I've looked at the sp_s mentioned above and individually they give me the info I need.
I'd like to combine them somehow.

This should get you started. its a bit rough and ready as i have been working on it for some time and not tidied it up yet. you can add multiple servers into the server_info table and the role info will be added to the server_roles table.
the only bit missing is the permissions part this should not be to difficult to add, i will be doing it in due course.

hope it helps


CREATE TABLE [dbo].[server_info] (
      [server_id] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [sqltype] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [sqlver] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [instance] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [available] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
      [info_gathered] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
      [Bootle] [char] (1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[server_roles] (
      [server_id] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [dbname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [member_name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [member_type] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
      [role_name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

create table server_dbs (
      [dbname] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
      )

insert into [server_info] (server_id)
      values
('<YOURSERVERNAME(s)>')

--update server_info set info_gathered ='n' -- this resets the marker for servers where info has been gathered.


declare @hdr_txt varchar(255),
                  @cmd nvarchar(1000),
            @objName nVARCHAR(500),
            @objName2 nVARCHAR(500),      
            @param nvarchar (500)
set nocount on

      --print 'params declared'
SET      @objName = ''
--print 'SET      @objName = '
 
WHILE      @objName IS NOT NULL
      BEGIN
            SELECT @objName = MIN( server_id)
            FROM      server_info
            WHERE      
                  server_id > @objName  and info_gathered ='n'
      print 'select next ' + @objname
            truncate table server_dbs
            IF      @objName IS NOT NULL
                  print '@objname not null'
                  set @cmd = 'insert into server_dbs select a.* from OPENROWSET (''SQLOLEDB'',''Server=' + @objname +';Trusted_Connection=Yes' +
                        ';Database=master'',''select name from sysdatabases '') as a'
            print @cmd      
            exec sp_executesql @cmd
            
                              
                  SET      @objName2 = ''

                        WHILE      @objName2 IS NOT NULL
                              BEGIN
                                    SELECT @objName2 = MIN(dbname)
                                    FROM      server_dbs
                                    WHERE      
                                    dbname > @objname2
                                    IF      @objName2 IS NOT NULL

                        print '@objname2 is not null'


                              set @cmd = 'insert into server_roles select a.* from OPENROWSET (''SQLOLEDB'',''Server=' + @objname +';Trusted_Connection=Yes' +
                                                      ';Database='  + @objname2 + ''',''select '''''+ @objname+ ''''' as server_id, ''''' + @objname2 + ''''' as dbname, u.name as member_name , case '+
                                          ' when u.isntgroup = 1 then ''''NT Group''''' +
                                          ' when u.isntuser = 1 then ''''NT User''''' +
                                          ' when u.issqluser = 1 then ''''SQL User''''' +
                                          ' when u.issqlrole = 1 then ''''DB Role''''' +
                                          ' end as member_type      ,r.name as role_name from      sysusers u' +
                                          ' left join      sysmembers m on (u.uid = m.memberuid)' +
                                          ' left join      sysusers r on (m.groupuid = r.uid)' +
                                          --' where      u.name  not in' +
                                          --' (''''dbo'''', ''''db_owner'''', ''''db_accessadmin'''', ''''db_securityadmin'''','+
                                          --'''''db_ddladmin'''', ''''db_backupoperator'''', ''''db_datareader'''', ''''db_datawriter'''',''''db_denydatareader'''', ''''db_denydatawriter'''',''''guest'''' )'') ' +
                                          ''' as a' -- remove the two extra ' at start of line to when including lines above
                              print @cmd
                              exec sp_executesql @cmd
                              end
                  update server_info
                        set info_gathered = 'y' where server_id = @objname
                  --truncate table #server_dbs
                        
      end


-- select * from server_info where available= 'y' and info_gathered is null
-- update server_info set info_gathered = null
--select * from server_roles
-- truncate table server_roles
-- select * from server_dbs
-- select * from server_info

sorry early morning.... after a reread i see that the above does not give you the users within the roles. this is something i am also doing to find out who is in the sa role

so far i have this. the last four lines weeds out the sa role.

select 'ServerRole' = spv.name, 'MemberName' = lgn.name, 'MemberSID' = lgn.sid
                  from master.dbo.spt_values spv, master.dbo.sysxlogins lgn
--                  where spv.low = 0 and  
--                          spv.type = 'SRV' and
--                          lgn.srvid IS NULL and
--                          spv.number & lgn.xstatus = spv.number

just needs the additional code around it.

btw some of this code has been gleaned from other sources and then adapted so my thanks to them whom ever they were.

If the sp's we supplied originally are enough for you, and you say they are just going to excel anyway, the quick and dirty method I would use is to just run them in QA then copy and paste them into excel where you can work with further manipulate them there.

If you need to do join on them before you move them you could insert the results of the sp's into tables and then run a select on the populated tables.  This may give you more flexibility in filtering out the roles you do not need.

To insert the results of an sp into a table, just create a table and then do an "insert into table execute sp_x".

For example :

create table #temp
( rolename varchar(50),
  roleid int,
  isapprole int
)

insert into #temp
execute sp_helprole

select * from #temp

drop table #temp


Hope this help,
Kent

Thanks but this only lists the roles.

I want something along the lines of.....

Role = Rolename
Members = John Smith
                  Jane Doe
                  .....
Permission = TableA S,I,U
                    TableB S
                    .....

Yes, that was just an example of how to do one of the sp's.  You would do that to all of them and then once they are in tables combine them using SQL to get the output you desire.

I'm getting close; still need to work out how to get all object permissions on the same line instead of on separate lines.  Please try this out and let me know.


IF OBJECT_ID('tempdb..#data') IS NOT NULL
      DROP TABLE #data
CREATE TABLE #data
 (
      ident SMALLINT IDENTITY(1,1),
      type TINYINT, --1=role;2=member;3=permissions
      roleUid SMALLINT,
      memberUid SMALLINT,
      objId INT,
      action TINYINT,
      protectType TINYINT
      ,CONSTRAINT data_ix
            UNIQUE CLUSTERED (roleUid, type, ident)
      )

SET NOCOUNT ON
INSERT INTO #data (type, roleUid, memberUid, objId, action, protectType)
SELECT *
FROM (
      SELECT 1 AS type, uid AS roleUid, 0 AS memberUid, 0 AS objId, 0 AS action, 0 AS protectType
      FROM sysusers WITH (NOLOCK)
      WHERE IsSqlRole = 1 AND uid > 16393
      UNION ALL
      SELECT 2, mem.groupUid, mem.memberUid, 0, 0, 0
      FROM sysmembers mem WITH (NOLOCK)
      INNER JOIN sysusers usr WITH (NOLOCK) ON usr.IsSqlRole = 1 AND usr.uid > 16393 AND
            usr.uid = mem.groupUid
      UNION ALL
      SELECT 3, mem.groupUid, 32767, prt.id, prt.action, prt.protectType
      FROM sysprotects prt WITH (NOLOCK)
      INNER JOIN sysmembers mem WITH (NOLOCK) ON mem.memberUid = prt.uid
      INNER JOIN sysusers usr WITH (NOLOCK) ON usr.IsSqlRole = 1 AND usr.uid > 16393 AND
            usr.uid = mem.groupUid
) AS derived
ORDER BY roleUid, type,
      CASE memberUid WHEN 32767 THEN REPLICATE(N'z', 30) ELSE USER_NAME(memberUid) END,
      OBJECT_NAME(objId), action

SELECT CASE WHEN type = 1 THEN 'Role'
      WHEN type = 2 THEN CASE WHEN EXISTS (
            SELECT 1 FROM #data d2
            WHERE d2.roleUid = d1.roleUid AND d2.type = 2 AND d2.ident < d1.ident )
            THEN '' ELSE 'Member(s)' END
      WHEN type = 3 THEN CASE WHEN EXISTS (
            SELECT 1 FROM #data d3
            WHERE d3.roleUid = d1.roleUid AND d3.type = 3 AND d3.ident < d1.ident )
            THEN '' ELSE 'Permission(s)' END END AS [Entry],
      CASE type
            WHEN 1 THEN USER_NAME(roleUid)
            WHEN 2 THEN USER_NAME(memberUid)
            WHEN 3 THEN CASE WHEN objId > 0 THEN OBJECT_NAME(objId) ELSE CASE action
                  WHEN 26 THEN 'REFERENCES'
                  WHEN 178 THEN 'CREATE FUNCTION'
                  WHEN 193 THEN 'SELECT'
                  WHEN 195 THEN 'INSERT'
                  WHEN 196 THEN 'DELETE'
                  WHEN 197 THEN 'UPDATE'
                  WHEN 198 THEN 'CREATE TABLE'
                  WHEN 203 THEN 'CREATE DATABASE'
                  WHEN 207 THEN 'CREATE VIEW'
                  WHEN 222 THEN 'CREATE PROCEDURE'
                  WHEN 224 THEN 'EXECUTE'
                  WHEN 228 THEN 'BACKUP DATABASE'
                  WHEN 233 THEN 'CREATE DEFAULT'
                  WHEN 235 THEN 'BACKUP LOG'
                  WHEN 236 THEN 'CREATE RULE' ELSE '?' END END END AS [Statement/Object],
      CASE WHEN type < 3 OR objId = 0 THEN '' ELSE CASE action
            WHEN 26 THEN 'REFERENCES'
            WHEN 178 THEN 'CREATE FUNCTION'
            WHEN 193 THEN 'SELECT'
            WHEN 195 THEN 'INSERT'
            WHEN 196 THEN 'DELETE'
            WHEN 197 THEN 'UPDATE'
            WHEN 198 THEN 'CREATE TABLE'
            WHEN 203 THEN 'CREATE DATABASE'
            WHEN 207 THEN 'CREATE VIEW'
            WHEN 222 THEN 'CREATE PROCEDURE'
            WHEN 224 THEN 'EXECUTE'
            WHEN 228 THEN 'BACKUP DATABASE'
            WHEN 233 THEN 'CREATE DEFAULT'
            WHEN 235 THEN 'BACKUP LOG'
            WHEN 236 THEN 'CREATE RULE' END END AS [Permission]
FROM #data d1

Looking good!!
I hope someone is paying you well :)
That is nice code.

Oh, also does *not* include any fixed db roles; am working on that now too :-) .

Thanks! (and, not well enough, of course! :-) ).

I have added the fixed db roles (too important to omit!), but still working on getting multiple permissions for the same object "combined".


IF OBJECT_ID('tempdb..#data') IS NOT NULL
      DROP TABLE #data
CREATE TABLE #data
 (
      ident SMALLINT IDENTITY(1,1),
      type TINYINT, --1=role;2=member;3=permissions
      roleUid SMALLINT,
      memberUid SMALLINT,
      objId INT,
      action TINYINT,
      protectType TINYINT
      ,CONSTRAINT data_ix
            UNIQUE CLUSTERED (roleUid, type, ident)
      )

SET NOCOUNT ON
INSERT INTO #data (type, roleUid, memberUid, objId, action, protectType)
SELECT *
FROM (
      SELECT 1 AS type, uid AS roleUid, 0 AS memberUid, 0 AS objId, 0 AS action, 0 AS protectType
      FROM sysusers WITH (NOLOCK)
      WHERE IsSqlRole = 1 AND uid > 16393
      UNION ALL
      SELECT 2, mem.groupUid, mem.memberUid, 0, 0, 0
      FROM sysmembers mem WITH (NOLOCK)
      INNER JOIN sysusers usr WITH (NOLOCK) ON usr.IsSqlRole = 1 AND usr.uid > 16393 AND
            usr.uid = mem.groupUid
      UNION ALL
      SELECT 3, usr.uid, mem.groupUid, 0, 0, 0
      FROM sysusers usr WITH (NOLOCK)
      INNER JOIN sysmembers mem WITH (NOLOCK) ON usr.uid = mem.memberUid
      WHERE usr.IsSqlRole = 1 AND usr.uid > 16393
      UNION ALL
      SELECT 4, mem.groupUid, 32767, prt.id, prt.action, prt.protectType
      FROM sysprotects prt WITH (NOLOCK)
      INNER JOIN sysmembers mem WITH (NOLOCK) ON mem.memberUid = prt.uid
      INNER JOIN sysusers usr WITH (NOLOCK) ON usr.IsSqlRole = 1 AND usr.uid > 16393 AND
            usr.uid = mem.groupUid
) AS derived
ORDER BY roleUid, type,
      CASE memberUid WHEN 32767 THEN REPLICATE(N'z', 30) ELSE USER_NAME(memberUid) END,
      OBJECT_NAME(objId), action

SELECT CASE WHEN type = 1 THEN 'Role'
      WHEN type = 2 THEN CASE WHEN EXISTS (
            SELECT 1 FROM #data d2
            WHERE d2.roleUid = d1.roleUid AND d2.type = 2 AND d2.ident < d1.ident )
            THEN '' ELSE 'Member(s)' END
      WHEN type IN (3, 4) THEN CASE WHEN EXISTS (
            SELECT 1 FROM #data d3
            WHERE d3.roleUid = d1.roleUid AND d3.type IN (3, 4) AND d3.ident < d1.ident )
            THEN '' ELSE 'Permission(s)' END END AS [Entry],
      CAST(CASE
            WHEN type = 1 THEN USER_NAME(roleUid)
            WHEN type = 2 THEN USER_NAME(memberUid)
            WHEN type = 3 THEN UPPER(USER_NAME(memberUid))
            WHEN type = 4 THEN CASE WHEN objId > 0 THEN OBJECT_NAME(objId) ELSE CASE action
                  WHEN 178 THEN 'CREATE FUNCTION'
                  WHEN 198 THEN 'CREATE TABLE'
                  WHEN 203 THEN 'CREATE DATABASE'
                  WHEN 207 THEN 'CREATE VIEW'
                  WHEN 222 THEN 'CREATE PROCEDURE'
                  WHEN 228 THEN 'BACKUP DATABASE'
                  WHEN 233 THEN 'CREATE DEFAULT'
                  WHEN 235 THEN 'BACKUP LOG'
                  WHEN 236 THEN 'CREATE RULE' ELSE '?' END END END AS VARCHAR(50))
      AS [{Member/Role | Statement | Object} Name],
      CASE WHEN type < 4 OR objId = 0 THEN '' ELSE CASE action
            WHEN 26 THEN 'REFERENCES'
            WHEN 178 THEN 'CREATE FUNCTION'
            WHEN 193 THEN 'SELECT'
            WHEN 195 THEN 'INSERT'
            WHEN 196 THEN 'DELETE'
            WHEN 197 THEN 'UPDATE'
            WHEN 198 THEN 'CREATE TABLE'
            WHEN 203 THEN 'CREATE DATABASE'
            WHEN 207 THEN 'CREATE VIEW'
            WHEN 222 THEN 'CREATE PROCEDURE'
            WHEN 224 THEN 'EXECUTE'
            WHEN 228 THEN 'BACKUP DATABASE'
            WHEN 233 THEN 'CREATE DEFAULT'
            WHEN 235 THEN 'BACKUP LOG'
            WHEN 236 THEN 'CREATE RULE' END END AS [Permission]
FROM #data d1

Do you need to drop the constraint also?
I get object data_ix already exists error even though the temp table it resides in has been dropped

No, not really, dropping the table should drop the constraint.

If a change has been made, though, SQL sometimes gets "confused".  Add a "GO" statement after the IF ... DROP line so that it runs in its own batch and/or run the temp table delete first by itself, then run the whole code.

tried both...
cannot delete table doesn't exist (as expected)

run in 2 parts (drop then create) or run in it's entirety I get

Server: Msg 2714, Level 16, State 4, Line 1
There is already an object named 'data_ix' in the database.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Can't seem to recreate this behaviour, perhaps it was in memory or something like that at the time.

Were you using Query Analyzer or EM?  I wouldn't be at all suprised by EM making errors like that.

QA - always

Scott
Where should this be run? I get no data at all

thanks