DDL create user in T-SQL stored proc/trigger

Hello SQL Server programming gurus:

I am trying to create a trigger that fires after a user logon and logoff and does the following:

creates a new user
deletes data from old temp table

Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access

We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?I really don't think you want to try that using a trigger. It sounds like a really bad idea to me.

First and foremost, I don't know of any way to launch a trigger on a login or logout event. You could probably approximate this using either SQL Profiler or a "watchdog" process, but the fundamental idea isn't supported by SQL Server itself, so you'll need some kind of "helper" to get the job done.

Next, I'd be really leary of having users created "on the fly" by anyone, much less what you seem to be describing where every login has the ability to create users! I can't imagine a scenario where that idea would appeal to me, unless it was to torment some dba that was going to inherit that code after I'd left. The thought positively gives me the willies!

The temp tables really aren't any problem... SQL Server simply drops temp tables after the user logs off. You really don't need to do any "care and feeding" of them, they are completely expendable.

If you can explain what you are trying to do (in English, not Transact-SQL), I'd be willing to bet that someone here can offer you some ideas that will help a bunch!

-PatP

dbo owner

We are using SQL Express and I have a problem with dbo.

On my machine if I logon to SQL Express using windows authentication

and create a new database I automatically have db_owner role membership

on the new database.

On a colleagues machine, if he logs onto his SQL Express using windows authentication

and creates a new database he does NOT have db_owner role membership

on the new database.

How come?

I have checked pretty much everythng - windows built in admins, SQL express sysadmin roles

service pack versions but I can not find any difference in setup.

What should I do to his machine/SQL Express setup so he automatically has db_owner role membership

for every newly created database?

Thanks

Charlie

Hi, this sounds strange. The database creator should have db_owner role memebership by default. Which ower is assigned to the new database ? Is it dbo ? Is your Account you are using for the creation of the database part of the sysadmin role ? Then you probably already have dbo Owner rights through the group memerbership.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I agree with Jens. This sounds strange, but most likely you get dbo access via sysadmin membership. I would first recommend trying the following query:

SELECT is_rolemember( 'db_owner' )

Members of db_owner as well as DBO should return 1 on the previous query.

I would also suggest trying the following query:

select name, type, usage from sys.login_token

order by type, usage, name

select name, type, usage from sys.user_token

order by type, usage, name

You should be able to see the login token as well as the user token on the current DB.

If the login token has an entry for sysadmin(sysadmin, SERVER ROLE, GRANT OR DENY) , then you should automatically be mapped to DBO on any DB. If you are not sysadmin, then pay attention to the user token.

Please let us know the results.

-Raul Garcia

SDE/T

SQL Server Engine