Assign name to the DB users.

I'm developing a vb 2005 application and I’m creating the users directly to the database. I want to assign them names.

I want to do something like this:

CREATE TABLE admin.db_users (

id INT CONSTRAINT db_user_pk PRIMARY KEY,

[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,

authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,

CONSTRAINT db_user_fk FOREIGN KEY(id)

REFERENCES sys.database_principals (principal_id)

ON UPDATE CASCADE

ON DELETE CASCADE

);

GO

This is the error that i'm getting:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How do I solve this problem or how can I do something similar.

You cannot create foreign key references to views. A different way to get functionality close to what you need would be to use insert/update trigger on the db_users table where the trigger action verifies the existance of the user in sys.database_principals. Then define a DDL trigger on the database for the DDL_USER_EVENTS (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm) to catch DROP/ALTER USER events and do the appropriate delete/cascade action.|||

Hi,

I’m new in these if you or some body could to help me I will appreciated. How can I create that trigger?

This is the script of the table that I want to use if this is possible:

CREATE TABLE admin.db_users (

idINT CONSTRAINT user_pk PRIMARY KEY,

[name]VARCHAR(50) CONSTRAINT user_name_nn NOT NULL,

authenticationVARCHAR(25) CONSTRAINT user_authentication_nn NOT NULL,

activeBIT

);

|||

Here's a sample that will give you a sense of what can be done and get you started. You will need to modify/build on the sample to achieve what you need. Please check the "CREATE TRIGGER" topic in Books Online for more detailed information on using triggers.

-- DML Trigger to verify users against sys.database_principals
--
create trigger check_users on db_users
for insert, update
as
-- Collect inserted users that don't exist in sys.database_principals
declare @.invalid_users table([name] sysname)
insert into @.invalid_users
select convert(sysname, name) from inserted except select [name] from sys.database_principals

-- If invalid users are found, rollback transaction
if exists (select [name] from @.invalid_users)
begin
print 'Operation was aborted because following users are invalid'
select [name] as invalid_users from @.invalid_users
if @.@.trancount > 0 rollback tran
end
go

-- DDL Trigger to catch create/drop/alter of new database principals
--
create trigger user_ddl_trig
on database
for ddl_user_events
as
declare @.user_name sysname,
@.event_type sysname

select @.event_type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
select @.user_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

-- Do your cascased action
if @.event_type = 'DROP_USER'
begin
print 'dropping user ' + @.user_name
delete from db_users where [name] = @.user_name
-- Or check against db_users and rollback this transaction, etc
end
--else if @.event_type = 'ALTER_USER'...
go

|||Thanks a lot.