Solved

Trigger t-sql

Posted on 2000-03-31
2
476 Views
Last Modified: 2008-03-03
I'd like the t-sql that would make a trigger which would,whenever a change in
                   permissions or indexes was made to a table or view, it would populate a table(logtable)
                   with getdate(), the id making the change , generate sql script of the original and the new
                   in directory called original and new.
                   If the points seem stingy, that can be negotiated.
Using version 6.5.
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
kpkp earned 50 total points
ID: 2679392
The 6.5 equivalent of the syspermissions table is SysProtects.  You could use insert/update and delete triggers on this table to record permission changes.  

sysprotects system table

Contains information about permissions that have been applied to security accounts with the GRANT and DENY statements. This table is stored in each database.

(from MSDN http://msdn.microsoft.com/library/psdk/sql/sys-p_3.htm)

Column      Data type            Description      
name

id            int                  ID of object to which these permissions apply.      
uid            smallint            ID of user or group to which these permissions apply.      
action            tinyint                  Can have one of these permissions:  
                              26 = REFERENCES
                              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      
protecttype      tinyint                  Can have these values:
                              204 = GRANT_W_GRANT
                              205 = GRANT
                              206 = REVOKE      
columns      varbinary(4000)      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.      
                              
grantor      smallint            User ID of the user who issued the GRANT or REVOKE permissions.      
0
 

Author Comment

by:xoxomos
ID: 2681681
Looks like its all there, but i'm not going after it correctly.  Trying to insert sysobjects.name, sysprotects.id, uid, action, protecttype into logtable, but nothing is giong into it.  I'll try to formulate another question.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question