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
Solved

Trigger t-sql

Posted on 2000-03-31
2
474 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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

840 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