Get the Table Columns info with SYSPROTECTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Get the Table Columns info with SYSPROTECTS

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2004-07-20 : 03:21:29

I use the following query to get the rights to all objects of my database for all users:

select P.ID, U.Name as UserName, o.name as ObjectName,
case P.columns
when 1 then 'All Columns'
else P.columns end as ColumnsInfo,
case P.ProtectType
when 204 then 'GRANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE'
end as ProtectType,
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'
end as PermissionGranted

from sysprotects P inner join sysusers U on P.Uid = U.UID inner join sysobjects O on P.ID=O.ID
order by UserName, ObjectName

How can I get the ColumnInfo column to display correctly the name of the column and not a bitmap.

The help on sysprotects says :
Bitmap of columns to which these SELECT or UPDATE permissions apply. Bit 0 indicates all columns; bit 1 means permissions apply to that column and NULL means no information.

Thanks Paul

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-20 : 03:55:04

Use the INFORMATION_SCHEMA (assuming SQL2000)

select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources