Which system table contains action descriptions

[SBendBuckeye]

Hello all,

I am using the following T-Sql to retrieve permissions granted to a given user.

-- Return permissions granted to specified user.
SELECT sp.*, so.[Name]
FROM sysProtects sp inner join
	 sysObjects so on sp.id = so.id
WHERE sp.uid = USER_ID('UserName')

One of the columns returned is Action. To what table do I link to get the action name (eg Execute)? Thanks in advance for any ideas and/or suggestions!

 

[philhege]

From BOL:

Can have one of these permissions:
26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE

These appear to be constants. You could create your own table with these values and join to your original query.

 

[bborissov]

From BOL under sysProjects topic:

action tinyint
Can have one of the following permissions:

26 = REFERENCES
178 = CREATE FUNCTION
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SBendBuckeye]

Thanks guys! Obviously had a major brain cramp.

 

[mrdenny]

This thread thread962-1324602 should have the total code that you are looking for in order to script out permissions. There's code from SQL 7-2005 in there.

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

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

http://www.mrdenny.com