How can I obtain the login name of the caller of my UDF written in a CLR language?

I am using MS SQL Server 2005.
Is there a way to obtain the login name of the caller of my user
defined function implement in managed C++?
I know I can get the information using the code segment below but I
want to avoid connecting back to the database for performance reasons.
String^ queryString = "select suser_sname()"
SqlConnection conn = gcnew SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);
conn.Open();
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnect ion);
while (reader.Read())
{
reader[0];
}
Thanks,
Steven
Examine using the Security object.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160176423.328986.327470@.i42g2000cwa.googlegr oups.com...
>I am using MS SQL Server 2005.
> Is there a way to obtain the login name of the caller of my user
> defined function implement in managed C++?
> I know I can get the information using the code segment below but I
> want to avoid connecting back to the database for performance reasons.
> String^ queryString = "select suser_sname()"
> SqlConnection conn = gcnew SqlConnection(connectionString);
> SqlCommand command = new SqlCommand(queryString, connection);
> conn.Open();
> SqlDataReader reader =
> command.ExecuteReader(CommandBehavior.CloseConnect ion);
> while (reader.Read())
> {
> reader[0];
> }
> Thanks,
> Steven
>
|||Darn Spell Checker.
Look into the SecurityPrinicipal object.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uBNLaka6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Examine using the Security object.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "smaully" <smauldin@.ingrian.com> wrote in message
> news:1160176423.328986.327470@.i42g2000cwa.googlegr oups.com...
>
|||Arnie,
That will return the Windows user not the sql login.
-Steve
|||I read that that is what you desired; "obtain the login name of the caller".
There was nothing to indicate you were using SQL Logins.
Since that is know to and in SQL Server, and not known to the client
application, the only way is to ask SQL Server to provide it. That may
require a 'wasted' round trip. You might call a stored procedure, have the
stored procedure call the UDF, and pass the SQL Login back as a output
parameter.
Of course, I don't know how you are using the UDF, so that may not work for
you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160415121.924478.80610@.m7g2000cwm.googlegrou ps.com...
> Arnie,
> That will return the Windows user not the sql login.
> -Steve
>

How can I obtain the login name of the caller of my UDF written in a CLR language?

I am using MS SQL Server 2005.
Is there a way to obtain the login name of the caller of my user
defined function implement in managed C++?
I know I can get the information using the code segment below but I
want to avoid connecting back to the database for performance reasons.
String^ queryString = "select suser_sname()"
SqlConnection conn = gcnew SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);
conn.Open();
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
reader[0];
}
Thanks,
StevenExamine using the Security object.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>I am using MS SQL Server 2005.
> Is there a way to obtain the login name of the caller of my user
> defined function implement in managed C++?
> I know I can get the information using the code segment below but I
> want to avoid connecting back to the database for performance reasons.
> String^ queryString = "select suser_sname()"
> SqlConnection conn = gcnew SqlConnection(connectionString);
> SqlCommand command = new SqlCommand(queryString, connection);
> conn.Open();
> SqlDataReader reader =
> command.ExecuteReader(CommandBehavior.CloseConnection);
> while (reader.Read())
> {
> reader[0];
> }
> Thanks,
> Steven
>|||Darn Spell Checker.
Look into the SecurityPrinicipal object.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uBNLaka6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Examine using the Security object.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "smaully" <smauldin@.ingrian.com> wrote in message
> news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>|||Arnie,
That will return the Windows user not the sql login.
-Steve|||I read that that is what you desired; "obtain the login name of the caller".
There was nothing to indicate you were using SQL Logins.
Since that is know to and in SQL Server, and not known to the client
application, the only way is to ask SQL Server to provide it. That may
require a 'wasted' round trip. You might call a stored procedure, have the
stored procedure call the UDF, and pass the SQL Login back as a output
parameter.
Of course, I don't know how you are using the UDF, so that may not work for
you.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160415121.924478.80610@.m7g2000cwm.googlegroups.com...
> Arnie,
> That will return the Windows user not the sql login.
> -Steve
>

How can I obtain the login name of the caller of my UDF written in a CLR language?

I am using MS SQL Server 2005.
Is there a way to obtain the login name of the caller of my user
defined function implement in managed C++?
I know I can get the information using the code segment below but I
want to avoid connecting back to the database for performance reasons.
String^ queryString = "select suser_sname()"
SqlConnection conn = gcnew SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
reader[0];
}
Thanks,
StevenExamine using the Security object.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>I am using MS SQL Server 2005.
> Is there a way to obtain the login name of the caller of my user
> defined function implement in managed C++?
> I know I can get the information using the code segment below but I
> want to avoid connecting back to the database for performance reasons.
> String^ queryString = "select suser_sname()"
> SqlConnection conn = gcnew SqlConnection(connectionString);
> SqlCommand command = new SqlCommand(queryString, connection);
> conn.Open();
> SqlDataReader reader => command.ExecuteReader(CommandBehavior.CloseConnection);
> while (reader.Read())
> {
> reader[0];
> }
> Thanks,
> Steven
>|||Darn Spell Checker.
Look into the SecurityPrinicipal object.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uBNLaka6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Examine using the Security object.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "smaully" <smauldin@.ingrian.com> wrote in message
> news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>>I am using MS SQL Server 2005.
>> Is there a way to obtain the login name of the caller of my user
>> defined function implement in managed C++?
>> I know I can get the information using the code segment below but I
>> want to avoid connecting back to the database for performance reasons.
>> String^ queryString = "select suser_sname()"
>> SqlConnection conn = gcnew SqlConnection(connectionString);
>> SqlCommand command = new SqlCommand(queryString, connection);
>> conn.Open();
>> SqlDataReader reader =>> command.ExecuteReader(CommandBehavior.CloseConnection);
>> while (reader.Read())
>> {
>> reader[0];
>> }
>> Thanks,
>> Steven
>|||Arnie,
That will return the Windows user not the sql login.
-Steve|||I read that that is what you desired; "obtain the login name of the caller".
There was nothing to indicate you were using SQL Logins.
Since that is know to and in SQL Server, and not known to the client
application, the only way is to ask SQL Server to provide it. That may
require a 'wasted' round trip. You might call a stored procedure, have the
stored procedure call the UDF, and pass the SQL Login back as a output
parameter.
Of course, I don't know how you are using the UDF, so that may not work for
you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160415121.924478.80610@.m7g2000cwm.googlegroups.com...
> Arnie,
> That will return the Windows user not the sql login.
> -Steve
>

How can I implement error handling within a SQL script that uses b

I am trying to incorporate error handling into a SQL script that creates
several database objects.
Those objects are referenced later in the script by other blocks of code.
It is my understanding that I need to use a new batch to reference an object
that was just created. I can do this by inserting a GO statement after the
object creation statement.
But how do I implement error handling within a SQL script that uses batches?
IOW I don't want a subsequent batch to execute if a previous batch failed.
If I didn't have separate batches in my script I could use a RETURN or GOTO
statement to handle errors. But with batches I have the following problem:
--use RETURN
PRINT '1'
GO
PRINT '2'
RETURN
GO
PRINT '3'
--OUTPUT
1
2
3
--use GOTO
PRINT '1'
GO
PRINT '2'
GOTO error
GO
PRINT '3'
ERROR:
PRINT 'ERROR'
--OUTPUT
1
Server: Msg 133, Level 15, State 1, Line 2
A GOTO statement references the label 'error' but the label has not been
declared.
3
ERROR
Is the only solution to break up the script to run as indivifual batchs and
then evaluate each one after execution?Use the
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE
id=object_id(@.v_objectname))
BEGIN
-- start processing
END
ELSE
BEGIN
PRINT @.v_objectname + ' was not created'
END|||Do you need a new batch to reference an object created in the current bacth?
try this
create table t1 (A int)
insert into t1 values(1)
select * from t1
drop table t1
Or did I misunderstand u?|||On Wed, 31 May 2006 10:36:02 -0700, Dave wrote:
(snip)
>But how do I implement error handling within a SQL script that uses batches
?
> IOW I don't want a subsequent batch to execute if a previous batch failed.[/color
]
Hi Dave,
There's no good answer for this. Since the cient sends just one batch to
the server, waits for it to execute, then sends the other one, there is
no simple way to have an error in batch #1 abort batch #2.
There are kludges, though. The easiest (but nastiest) is to use a
RAISERROR with a severity of at least 20 - those are considered fatal,
and prompt SQL Server to terminate the connection after sending the
error message.
Somewhat more work (but still the version I'd prefer) is to use a table
to carry over status information. This can be a temp table. For
instance:
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
Hugo Kornelis, SQL Server MVP

How can I implement error handling within a SQL script that us

Hi Hugo,
I was thinking in similar lines as the solution you gave (not the one
with severity 20.. I would have never come up with such a solution :)
But I think there is a problem in the other one. We are talking about
different batches. and these batches will be creating database objects.
Can you give an example on how will you implement the error handling with
temp tables for this scenario?
1. Create a view on some existing table first (select *)
2. If the view creation is successful,then create another view on this view.On Thu, 1 Jun 2006 22:35:01 -0700, Omnibuzz wrote:
(snip)
>Can you give an example on how will you implement the error handling with
>temp tables for this scenario?
>1. Create a view on some existing table first (select *)
>2. If the view creation is successful,then create another view on this view.[/color
]
Hi Omnibuzz,
There's one thing I hadn't thought about - CREATE VIEW must be the only
in a batch, so you'll have to use dynamic SQL to make it conditional.
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
EXEC ('CREATE VIEW v1
AS
SELECT 1 AS a')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
EXEC ('CREATE VIEW v2
AS
SELECT a
FROM v1')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
go
Hugo Kornelis, SQL Server MVP

How can I find when a password is close to expiring

I am using VB6 with SQL server 2005. In order to implement password expiry properly I need to know how to find out when a user's password is due to expire so that I can output a message to prompt him to change his password. How can I interrogate this information?

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

cheers

U

How can I find when a password is close to expiring

I am using VB6 with SQL server 2005. In order to implement password expiry properly I need to know how to find out when a user's password is due to expire so that I can output a message to prompt him to change his password. How can I interrogate this information?

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

cheers

U