Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trigger t-sql

Posted on 2000-03-31
2
Medium Priority
?
486 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 200 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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