script db object permissions

I'm looking into processes to move to using database specific roles and migrating specifically assigned user permissions to these roles. I have made progress on listing permissions in the public role and assigning these same permissions to the new role that I have created for the given database. However, when I inspect permissions on tables and other objects, permissions have also been specifically granted for application users in the db. My ojective is to script out the permissions against the database objects (mainly user tables) and then use the output to assign the same permissions to the appropriate role instead. However, I'm having difficulty finding which system table stores the table and column level permissions.

If I do select * from sysobjects, I can see the user tables. However, I can't find the related tables that store user object permissions. For example, sysprotects doesn't store this info and, if I do an inner join on 'id' between these two tables, I don't see any user tables. Where do I look? Are there any useful 'already done' scripts/procs around for these purposes?

Regards,

CliveTry this one..
sp_helprotect
sskris|||well
i dont have an sql server with me now, but you could create a query between the sysobjects, sysusers, and sysprotects table and you could interpret the action and the protecttype columns
[Books Online] sysusers
[Books Online] sysobjects
[Books Online] sysprotects

if you want a quick way to view system tables in a graphical format you should download systbl.chm (http://download.microsoft.com/download/SQLSVR2000/sysmap/2000/WIN98MeXP/EN-US/systbl.chm) which is also available in your sql server 2000 resource kit.|||/*
This script creates a view to display users and objects that they have permissions for
and the permissions that are set
*/

use master
go
Create View VUserRights
as
SELECT top 100 percent
U.[Name] as UName
,O.Name as OName
,case xtype
when 'S' then 'System Table'
when 'P' then 'Stored Procedure'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'L' then 'Log'
when 'FN' then 'Scalar Function'
when 'IF' then 'Inlined Table-Function'
when 'PK' then 'PRIMARY KEY'
when 'RF' then 'Replication Filter Stored Procedure'
when 'S' then 'System Table'
when 'TF' then 'Table Function'
when 'TR' then 'Trigger'
when 'U' then 'User Table'
when 'UQ' then 'UNIQUE Constraint'
when 'V' then 'View'
when 'X' then 'Extended Stored Procedure'
else cast(xtype as varchar(30))
end as XType

,Case p.[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 cast([Action] as varchar(20))
End as 'Action'

,Case p.protecttype
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE'
Else cast(protecttype as varchar(20))
end as ProtectType

FROM sysusers U join sysprotects P
on u.uid = P.uid
Join sysobjects O
on P.id = O.id
where xtype <>'s'
order by U.uid ASC, O.xtype Desc

/*
Here are some calling statements
--2 is an oracle trick that i learned to
create a permissions assignment statement from exisiting metadata
*/

GO
--1
select * from vuserrights
Go
--2
select Protecttype + ' ' +
Action + ' ON ' +
Oname
-- +'('+ Xtype+')'
+ ' TO ' + Uname from vuserRights|||Thank you for the info. Nice View by the way.

I ended up writing a script that used sp_helprotect. It dumps out the permissions for a given user to a temp table and then I cursor through the temp table to 'grant' the permissions to a new role. eg. give me the Public permissions that have been granted against objects in this db and copy them to a new db role. Also, I can revoke the permsissions on Public as an option. So, I simply do:-

set @.GrantSQL ='Grant ' + @.action + ' on [' + @.Obj + '] to ' + @.Role
exec(@.GrantSQL)

However, although the above works fine on the db's I'm working on, I suspect that it wouldn't work with regard to database that have column level permssions. I tried setting a column level permission to see how my script would handle it. sp_helprotect reports the column level permssion but I haven't got around to modifiying my script to deal with it properly - currently it migrates the column level permssion in the source user/role to the target role as a table level permisson. In the loop that cursors through the temp table containing the sp_helprotect output, I presumably need to detect a column level permssion and branch to execute a grant statement that will apply a column level permission? Haven't had time to research this yet.

Thanks again,

Clive|||If you are comfortable with VB or VC, then SQL-DMO (http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_con01_2yi7.asp) offers exactly what you want.

-PatP|||Pat,

I am comfortable with VB but haven't doen anything with dmo yet. Got any examples?

Cheers,

Clive|||Just follow the link. There are zillions of examples scattered on the appropriate pages.

-PatP|||Zillions ?
it's more like a Quintillion.|||Zillions ?
it's more like a Quintillion.Ok, ok, ok... So what's a few zeros here and there between friends, eh? ;)

-PatP