Hi,

I have a table which includes a ([login_sname] sysname) column (used
for row-level security, SUSER_SNAME() joins). However, I am having
trouble figuring out how to add a FOREIGN KEY (login_sname) constraint,
which REFERENCES valid server logins for a given database. Using
[sys].[database_principals], [name] fails.

Any suggestions/pointers? Did I miss something in the docs?

Thanks,

Gary

Gary
I'm not sure , why would you want to do that?

As I understand you want the colummn to prevent from unwanted users to
manipulate with data,so

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm








Show quoteHide quote

Hi,

I have base tables wrapped in views, and, say, an INSTEAD OF INSERT
trigger checks not only whether a particular server login has been
authorized to run a query on the object level, but also whether that
server login has been authorized at the application level
(SUSER_SNAME() has to appear in a business user list).

Effectively, if a sever login is dropped, I want to CASCADE into the
business user table, so if a login is re-crated with the same name, it
will have to be explicitly re-authorized.

Gary

sys.database_principals is not a table, it's a view so you can't set a
foreign key to it.  You can use a trigger to enforce the relationship.

David

Hi,

Yes, indeed. I was hoping for an equivalent system table, or some kind
of server-specific FOREIGN KEY syntax.

I see your point, thanks for the tip. However, setting up an AFTER
DELETE trigger on sys.database_principals, I am getting:

Msg 8197, Level 16, State 4, Procedure
IO_delete_sys_database_principals, Line 2
Object 'sys.database_principals' does not exist or is invalid for this
operation.

Same thing with sys.server_principals, etc. Something I am screwing up?

Gary

Ok, for others interested in the topic as well, I'm replying to myself
This is doable in SQL Server 2005 only with system-wide DDL triggers
(CREATE TRIGGER ... ON ALL SERVER FOR DDL_LOGIN_EVENTS). To process the
information, DECLARE an xml type variable, and load it with EVENTDATA()
function. Resulting XML tree includes ObjectType of LOGIN, EventType of
DROP_LOGIN, ALTER_LOGIN, etc. and (new) object information as SID,
ObjectName, etc. Note that there is no inserted, deleted special table
functionality in DDL triggers, so you need to reference old login name
by SID to update your third-party table.

Sample trigger below:

CREATE TRIGGER IO_some_table_ddl_login_events
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
BEGIN

    DECLARE @event_data xml
    SET @event_data = EVENTDATA()

    DECLARE @event_type sysname
    DECLARE @object_type sysname

    SET @event_type = @event_data.value('(/EVENT_INSTANCE/EventType)[1]',
'sysname');
    SET @object_type =
@event_data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname');

    DECLARE @login_sid varbinary(85)
    DECLARE @login_sname sysname

    IF (@object_type = 'LOGIN')
    BEGIN

        IF (@event_type = 'DROP_LOGIN')

        BEGIN
            SET @login_sid = @event_data.value('(/EVENT_INSTANCE/SID)[1]',
'varbinary(85)');

            UPDATE [database_name].[dbo].[table_name]
            SET
                [login_sid] = NULL,
                [login_sname] = NULL
            WHERE
                [login_sid] = @login_sid
        END -- DROP_LOGIN

        ELSE IF (@event_type = 'ALTER_LOGIN')

        BEGIN
            SET @login_sid = @event_data.value('(/EVENT_INSTANCE/SID)[1]',
'varbinary(85)');
            SET @login_sname =
@event_data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

            UPDATE [database_name].[dbo].[table_name]
            SET
                [login_sname] = @login_sname
            WHERE
                [login_sid] = @login_sid
        END -- ALTER_LOGIN

    END -- LOGIN
END
GO

Sample events (since those are not to be found in SQL Server 2005
documentation):

   <EVENT_INSTANCE>
      <EventType>CREATE_LOGIN</EventType>
      <PostTime>2006-07-18T14:58:37.607</PostTime>
      <SPID>54</SPID>
      <ServerName>WKS\SQLEXPRESS</ServerName>
      <LoginName>WKS\Admin</LoginName>
      <ObjectName>oldloginname</ObjectName>
      <ObjectType>LOGIN</ObjectType>
      <DefaultLanguage>us_english</DefaultLanguage>
      <DefaultDatabase>my_database</DefaultDatabase>
      <LoginType>SQL Login</LoginType>
      <SID>sPnq583rjUuvZrTmAqlLqQ==</SID>
   </EVENT_INSTANCE>

   <EVENT_INSTANCE>
      <EventType>ALTER_LOGIN</EventType>
      <PostTime>2006-07-18T15:04:19.210</PostTime>
      <SPID>54</SPID>
      <ServerName>WKS\SQLEXPRESS</ServerName>
      <LoginName>WKS\Admin</LoginName>
      <ObjectName>newloginname</ObjectName>
      <ObjectType>LOGIN</ObjectType>
      <DefaultLanguage>us_english</DefaultLanguage>
      <DefaultDatabase>my_database</DefaultDatabase>
      <LoginType>SQL Login</LoginType>
      <SID>sPnq583rjUuvZrTmAqlLqQ==</SID>
   </EVENT_INSTANCE>

   <EVENT_INSTANCE>
      <EventType>DROP_LOGIN</EventType>
      <PostTime>2006-07-18T15:04:36.213</PostTime>
      <SPID>54</SPID>
      <ServerName>WKS\SQLEXPRESS</ServerName>
      <LoginName>WKS\Admin</LoginName>
      <ObjectName>newloginname</ObjectName>
      <ObjectType>LOGIN</ObjectType>
      <DefaultLanguage>us_english</DefaultLanguage>
      <DefaultDatabase>my_database</DefaultDatabase>
      <LoginType>SQL Login</LoginType>
      <SID>sPnq583rjUuvZrTmAqlLqQ==</SID>
   </EVENT_INSTANCE>

Hope this helps someone.

G.

sql query - sp
How can I Rearrange my Data
tring’
Query taking long-long time
Timestamp in User-Defined Function Causes Error