Login Event! (or something like that)

Hi,
I need to have something like a "login event" so that whenever a user logs
into SQL server, I can execute certain commands or SPs. So far, I've been
using this event in my client app that when a user clicks on the login
button, I do those jobs. But I want SQL server to handle and perform these
activities automatically and recognize each login.
Any help would be greatly appreciated.
AminTHere is no such event inside SQL Server.
AND you can't put triggers on SYSprocesses (which would be the next thing to
try).
You can write a proc that watches sysprocesses for new connections, or
you can catch logins with profiler and write a program that watches the
profiler output or
you can catch logins in the SQL error log (turn this on via SQL Enterprise
Manager on the Server->security tab and write a program that watches the SQL
log but
NONE of these will allow you to run proces etc on the newly connected
thread...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23qLxNBACEHA.3784@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need to have something like a "login event" so that whenever a user logs
> into SQL server, I can execute certain commands or SPs. So far, I've been
> using this event in my client app that when a user clicks on the login
> button, I do those jobs. But I want SQL server to handle and perform these
> activities automatically and recognize each login.
> Any help would be greatly appreciated.
> Amin
>

Login Access

I want to give a login the ability to see all database objects, insert and
and delete data, run stored procedures, and execute objects within a specifi
c
database. I do not want them to create or delete any database objects or
adjust security. How do I accomplish that by using the database roles?
>I want to give a login the ability to see all database objects, insert and
> and delete data, run stored procedures, and execute objects within a
> specific
> database. I do not want them to create or delete any database objects or
> adjust security. How do I accomplish that by using the database roles?
With a user-defined role. There is no predefined role for executing
procedures. Check the permissions of the fixed db oles in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a08108a3-f1fb-43ac-a264-3f2f
9749db5d.htm.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/

logging the DDL stmts

Hi :

I am using .cmd files to execute .sql files.

sqlcmd is used in .cmd files to execute the .sql stmts.

In .cmd file the sqlcmd line of code is as follows:

sqlcmd -i .\..\..\sql\tables\create_employee_table.sql

In .sql file the ddl stmt is as follows:

CREATE TABLE [Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] [int] NOT NULL,
[LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] [int] NULL,
DF_Employee_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

DDL used are create and drop of tables and indexes.

How do i log all the ddl stmts executed into some .log file (xyz.log)?

The log file should read something like,

employee table created sucessfully

employee table dropped sucessfully.

...................

sqlcmd -o c:\log\xyz.log , gives me only the output for dml stmts (like select * from emp).. anything other than this will be very helpful.

Any solutions will be of great help.

This is a broader SQL question than just SQL Express so I'm moving it to the Database Engine forum; I think you'll find a better answer there.

In looking around a bit, I found information about DDL Triggers which would seem to do what you suggest. The folks in the other forum can validate my guess.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

From the sounds of it you just want output in a text file to know whether or not your create table statement succeeded or not. DDL triggers can do this on the server, however if you have malformed sql (as in your post) or just want to log out to a text file directly from sqlcmd have a look at the -r parameter

sqlcmd -icreate_employee_table.sql -S. -E -o create_employee_table.log -r1

The file "create_employee_table.log" will contain output like this:

Msg 102, Level 15, State 1, Server name, Line 7
Incorrect syntax near ']'.
Msg 319, Level 15, State 1, Server name, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

To print out success messages you'll need to actually check the value of the @.@.error in the script and print out an appropriate message.

CREATE TABLE [Employee](
[EmployeeID] int IDENTITY(1,1) NOT NULL,
[NationalIDNumber] nvarchar(15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] int NOT NULL,
[LoginID] nvarchar(256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] int NULL,
[DF_Employee_rowguid] UNIQUEIDENTIFIER DEFAULT (newid()),
[ModifiedDate] datetime NOT NULL CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

if @.@.error = 0
print 'employee table created sucessfully'

Logging of SSIS package doesnt work when executed as a job

I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.

The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.

I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.

Thanks

Look at my post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=328057&SiteID=1

I have the same problem, but have also do some research and as I think the problem lays in something more complicated than running SSIS package as a job - it is only a symptom.

I have also found some roundtrip (not the best...) but it works on my development machine.

If you will have some new thoughts how to solve it please write!

Przemo

|||

Have Sql Agent run a command processor batch job that calls DtExec. If your package runs fine in DtExec it seems it would run fine when DtExec is called from a batch file?

Ken

|||

Look again at my post. There is a solution for the problem which helped me.

Now I use "Windows Integrated Security" in my sql connection string, so there is no problem with encrypting passwords. When I use SQL Login it must be used together with password encrypting SSIS, so I can provide the password at command line in SQL Job.

Przemo

|||

Hi,

Thanks for the reply , I have tried all what you replied it still dint work.

I am running a 32 bit SQl on a 64 bit machine and also have imports from an Excel source, if that can make things different. I know that there can be two problems why it doesnt work.

1) I developed the package in BIDS with my login and I am trying to deploy it in SQl server. The sql server agent is using proxies of my credentials to run. Is ther a different way it should be done?

2) Since i am running on a 64 bit machine, I set Run64BitRuntime to false, thinking that the providers (especially jet is 32 bit). I dont have program files(x86) on my computer. Should that be on the Sql server? If that is the case please let me know how to point to a different dtexec(32 bit).

Please let me know the exact settings that I need to do. One last question will the problem vanish if I use a 64 bit SQl server?

Thanks

|||I finally figured it out. It was a combination of the above two plus some authentication problems. The logging problem was due to pointing at a file outside of sql server.