long transaction

Hi,

I am still not very proficient in SQLServer. So apology if the
question sounds basic.

We have a script to clean old unwanted data. It basically deletes
all rows which are more than 2 weeks old. It deletes data from
33 tables and the number of rows in each table runs into few millions.
What I see in the script (not written by me :-) ) is that all data is
deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
I have background in informix, such an action in Informix may result
in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

Also won't it have performance problem if all rows are marked locked
till they are committed.

TIA."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:bq071h$1tbn6v$1@.ID-75254.news.uni-berlin.de...
> Hi,
> I am still not very proficient in SQLServer. So apology if the
> question sounds basic.
> We have a script to clean old unwanted data. It basically deletes
> all rows which are more than 2 weeks old. It deletes data from
> 33 tables and the number of rows in each table runs into few millions.
> What I see in the script (not written by me :-) ) is that all data is
> deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
> I have background in informix, such an action in Informix may result
> in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.

I'm not sure what the "LONG TRANSACTION PROBLEM" in Informix is, but yes,
you basically don't want transactions to run for long periods of time.

As you point out, this can cause blocking on reads depending on your
isolation level. In addition, it can make recovery that much harder.

Assume your transaction takes 20 minutes to run (that's a really long time
admittedly) and your server gets rebooted 19 minutes into it. You're
looking at probably at least 19 minutes of rollback time when you reboot.
During this time the database will be completely inaccessible.

You have a couple of options that depend on your environment.

One is to simply break this up into multiple transactions. Of course then
it depends on what happens if one fails. Does this matter to other
transactions? Are their any dependencies?

It may also be possible to copy the needed data into a temp table, truncate
the original table and move data back.

But regardless, I would try to redesign this.

> Also won't it have performance problem if all rows are marked locked
> till they are committed.
> TIA.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message news:<bq071h$1tbn6v$1@.ID-75254.news.uni-berlin.de>...
> Hi,
> I am still not very proficient in SQLServer. So apology if the
> question sounds basic.
> We have a script to clean old unwanted data. It basically deletes
> all rows which are more than 2 weeks old. It deletes data from
> 33 tables and the number of rows in each table runs into few millions.
> What I see in the script (not written by me :-) ) is that all data is
> deleted within a single BEGIN TRANSACTION and COMMIT TRANSACTION. As
> I have background in informix, such an action in Informix may result
> in "LONG TRANSACTION PROBLEM". Does SQLServer have a similar concept.
> Also won't it have performance problem if all rows are marked locked
> till they are committed.
> TIA.

If the data is unwanted, it seems unusual to delete it like that,
unless there really is a genuine requirement to do it inside a single
transaction. A large transaction like that can easily cause
performance and locking problems.

Assuming there isn't a real need to use a single transaction, then you
could do batch deletes instead, perhaps as an overnight maintenance
job to prevent users seeing unexpected results if they run queries
during the deletion.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote
> If the data is unwanted, it seems unusual to delete it like that,
> unless there really is a genuine requirement to do it inside a single
> transaction.

I don't see any such requirement. It can always continue next time if
the delete fails for some reason.

> A large transaction like that can easily cause
> performance and locking problems.

That's what I thought.

> Assuming there isn't a real need to use a single transaction, then you
> could do batch deletes instead, perhaps as an overnight maintenance
> job to prevent users seeing unexpected results if they run queries
> during the deletion.

I plan to remove BEGIN and COMMIT. This way each delete will be atomic
by itself.

thanks.

Long Data type in Sqlserver 2000

Dear friends,

I want to know that is the alternative data type of long in sqlserver

regards,

asad

Hi,
You can use one of the text, ntext or image data types in SQL Server for data upto 2 GB size.

Logon failed for user '(null)'..... error

We are receiving the error "Logon failed for
user '(null)'. Reason: Not associated with a trusted SQL
Server Connection".
We have a WinForms application written in VB.NET 2003 and
connect to a SQL Server 2000 (SP3) DB with the following
connection string:
gstrConn = _
"Server=SQL2000;" & _
"DataBase=dbname;" & _
"Integrated Security=SSPI;"
The application works perfectly for the users on one
network domain, but the error listed above appears when
trying to access the DB application from a user's
workstation on another domain. The user has a SQL Server
account. We have even tried to have the user run the DB
application in a smart client "trusted assembly"
environment but also get the error. What must be
configured differently and how?
A poor state employee-
Jeromeprevious message: "The application works perfectly for the users on one
network domain, but the error listed above appears when
trying to access the DB application from a user's
workstation on another domain. The user has a SQL Server
account. "
To make a Trusted connection the domains need to have a Trust between them.
The users need to be defined as either a domain user or from a domain
group.
If the user has a SQL Server account, then you are not using Integrated
Security. This is SQL Security. If you want to use SQL Security then you
need to change your connection string. If you want to keep the connection
string as is, then make sure the clients in the other domain can make a
Trusted Connection to SQL. Add the users from the other domain in SQL
Enterprise Manager.
Then, either load the SQL client tools on one of the workstations & test
connections from Query Analyser or try creating a sample ODBC DSN and
request Windows Authentication.
Read this information and it will make more sense.
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Cheif!
Unfortunately, I am not the network or SQL Server
Administrator but I am passing the information on to the
LAN/SQL Administrator. Hopefully, they will know how to
set up the trust between the domains since they are all
within our same division.
My connection string is fine for using Windows
Authentication right?
Also- All users no matter what domain they are on should
be able to access the DB if the domains are trusted, right?
(and the users have SQL Server IDs)
Sorry- I am LAN ignorant and overwhelmed at present trying
to get a grip on VB.NET. (Some finger pointing going own
here but we are civil and friendly).
Ailing in Alabama
Jrome
Merry Christmas from Dixie!

quote:

---

>--Original Message--
>previous message: "The application works perfectly for

---

the users on one

quote:

---

>network domain, but the error listed above appears when
>trying to access the DB application from a user's
>workstation on another domain. The user has a SQL Server
>account. "
>To make a Trusted connection the domains need to have a

---

Trust between them.

quote:

---

> The users need to be defined as either a domain user or

---

from a domain

quote:

---

>group.
>If the user has a SQL Server account, then you are not

---

using Integrated

quote:

---

>Security. This is SQL Security. If you want to use SQL

---

Security then you

quote:

---

>need to change your connection string. If you want to

---

keep the connection

quote:

---

>string as is, then make sure the clients in the other

---

domain can make a

quote:

---

>Trusted Connection to SQL. Add the users from the other

---

domain in SQL

quote:

---

>Enterprise Manager.
>Then, either load the SQL client tools on one of the

---

workstations & test

quote:

---

>connections from Query Analyser or try creating a sample

---

ODBC DSN and

quote:

---

>request Windows Authentication.
>
>Read this information and it will make more sense.
>http://www.microsoft.com/technet/treeview/default.asp?

---

url=/technet/prodtechn

quote:

---

>ol/sql/maintain/security/sp3sec/default.asp
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

---

confers no rights.

quote:

---

>
>.
>

---

|||Previous post questions:
My connection string is fine for using Windows
Authentication right?
-- Yes.
Also- All users no matter what domain they are on should
be able to access the DB if the domains are trusted, right?
-- Yes. They should have SQL logins defined by NT users or groups.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

LogMiner utility - SQLServer 2000

Is there any similar utility like Oracle LogMiner in SQLServer2000 or 2005?
Thanks, VinnieGoogle Lumigent Log Explorer

LoginName #

SQLServer is the Database. GUIs show Login name. Sometimes the login name
changes to 'Login #' ... like 'Mary 2'. What might make this happen?Hi Ann
Which version of SQL Server and screen is this? Are these SQL Logins or
Windows?
Have you checked syslogins to see if the logins exist?
John
"Ann" wrote:
> SQLServer is the Database. GUIs show Login name. Sometimes the login name
> changes to 'Login #' ... like 'Mary 2'. What might make this happen?
>|||Hi
The version is the most recent though I can not quote it.
SQL Server login/authentication.
The logins exist without the ' #' to their right.
Ann
"John Bell" wrote:
> Hi Ann
> Which version of SQL Server and screen is this? Are these SQL Logins or
> Windows?
> Have you checked syslogins to see if the logins exist?
> John
> "Ann" wrote:
> > SQLServer is the Database. GUIs show Login name. Sometimes the login name
> > changes to 'Login #' ... like 'Mary 2'. What might make this happen?
> >
> >|||My reply did not seem to post so I am re posting : ).
SQL Server version most recent
SQL Logins
The logins without the ' #' exist.
"John Bell" wrote:
> Hi Ann
> Which version of SQL Server and screen is this? Are these SQL Logins or
> Windows?
> Have you checked syslogins to see if the logins exist?
> John
> "Ann" wrote:
> > SQLServer is the Database. GUIs show Login name. Sometimes the login name
> > changes to 'Login #' ... like 'Mary 2'. What might make this happen?
> >
> >|||Hi Ann
So what does SELECT @.@.VERSION return? You did not say which screen this is?
Does sp_who2 also return these logins?
John
"Ann" wrote:
> My reply did not seem to post so I am re posting : ).
> SQL Server version most recent
> SQL Logins
> The logins without the ' #' exist.
>
> "John Bell" wrote:
> > Hi Ann
> >
> > Which version of SQL Server and screen is this? Are these SQL Logins or
> > Windows?
> > Have you checked syslogins to see if the logins exist?
> >
> > John
> >
> > "Ann" wrote:
> >
> > > SQLServer is the Database. GUIs show Login name. Sometimes the login name
> > > changes to 'Login #' ... like 'Mary 2'. What might make this happen?
> > >
> > >|||I can not verify SELECT @.@.VERSION
These logins are not in any log or registered anywhere. It is a valid login
name with a number/# to the right ... the longer the user sits in the gui
configured room ... the greater the number/# gets ... increasing by 1 every
20 minutes.
Hope this helps,
Anna (Project Manager)
"John Bell" wrote:
> Hi Ann
> So what does SELECT @.@.VERSION return? You did not say which screen this is?
> Does sp_who2 also return these logins?
> John
> "Ann" wrote:
> > My reply did not seem to post so I am re posting : ).
> >
> > SQL Server version most recent
> > SQL Logins
> > The logins without the ' #' exist.
> >
> >
> > "John Bell" wrote:
> >
> > > Hi Ann
> > >
> > > Which version of SQL Server and screen is this? Are these SQL Logins or
> > > Windows?
> > > Have you checked syslogins to see if the logins exist?
> > >
> > > John
> > >
> > > "Ann" wrote:
> > >
> > > > SQLServer is the Database. GUIs show Login name. Sometimes the login name
> > > > changes to 'Login #' ... like 'Mary 2'. What might make this happen?
> > > >
> > > >|||Hi Ann
Unfortunately the information you have given does not help me to diagnose
this problem. Without basic information such as what tool you are using and
which version of SQL Server you are using I can not really progress. Normally
with either Enterprise Manager (for SQL 2000 ) or SQL Server Management
Studio (SQL 2005) the current activity screens would reflect the actual login
name without change, therefore the information you have posted does not give
me any insight in why it should be different in your case.
John
"Ann" wrote:
> I can not verify SELECT @.@.VERSION
> These logins are not in any log or registered anywhere. It is a valid login
> name with a number/# to the right ... the longer the user sits in the gui
> configured room ... the greater the number/# gets ... increasing by 1 every
> 20 minutes.
> Hope this helps,
> Anna (Project Manager)
> "John Bell" wrote:
> > Hi Ann
> >
> > So what does SELECT @.@.VERSION return? You did not say which screen this is?
> > Does sp_who2 also return these logins?
> >
> > John
> >
> > "Ann" wrote:
> >
> > > My reply did not seem to post so I am re posting : ).
> > >
> > > SQL Server version most recent
> > > SQL Logins
> > > The logins without the ' #' exist.
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Ann
> > > >
> > > > Which version of SQL Server and screen is this? Are these SQL Logins or
> > > > Windows?
> > > > Have you checked syslogins to see if the logins exist?
> > > >
> > > > John
> > > >
> > > > "Ann" wrote:
> > > >
> > > > > SQLServer is the Database. GUIs show Login name. Sometimes the login name
> > > > > changes to 'Login #' ... like 'Mary 2'. What might make this happen?
> > > > >
> > > > >

Login/User problems after restore

:confused:

I have a question regarding SQLSERVER. I have 2 systems, production and development. I have restored a copy of the production database into the develeopment server to do some testing but am now having problems connecting to the database.

The issue
======

The database tables in the restored database are owned by a user HEATPROD in production and are stiil owned by that user after the restore. The USERS tab in SQL Server Enterprise Manager for this database does not show the user HEATPROD although the TABLES tab shows that user as owning the tables.

I have created the LOGIN of HEATPROD but am not able to grant this login access to the restored database as I get the error "ERROR 15023: USER OR ROLE HEATPROD ALREADY EXISTS IN THE CURRENT DATABASE".

An attemp to login as the HEATPROD user and access the new database generates the following error "SERVER USER HEATPROD IS NOT A VALID USER IN DATABASE DBATESTDB" however attempts to add this user to the database generate the 15023 error above.

Any suggestion on how to link the LOGIN to the USER and thus gain access to the database would be much appreciated.

TIAIt sounds as if you have an "orphaned user" in your database called HEATPROD.

To check this run the following

Use <Insert DB Name>
go
Select suid, name from sysusers
where name = 'HEATPROD'

If it does exist you need to delete it from this table.
To do that run the following ....

Use <Insert DB Name>
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
delete from sysusers where name = 'HEATPROD'
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go

When you have deleted the user, Refresh your database through Enterprise Manager.

You can now add the user to your database .... providing a login exists of course.

Hope this helps

P.C. Vaidyanathan|||The problem is that in the master database there is no user with that name.
This can happen when you restore a dabase to another server or domain. Look at microsoft for the solution Q218172|||Running

EXEC sp_change_users_login 'Auto_Fix', 'HEATPROD'

Should do the trick if you are running sql server authentication

Rosko|||Thanks,

It worked as suggested.:)|||This is a script I use to correct logins after restoring from one server to another. The results can be copied to the active pane (where you can run the script) to fix all orphaned users for the database in question.

select 'sp_change_users_login @.Action = ' + char(39) + 'auto_fix' +
char(39) + ', @.usernamepattern = ' + char(39) + name + char(39) + char(13) + 'go'
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name

Hope this helps.

Rory

Login/User problem when copying SQL database

Hi,
I'm always having problems when I want to move a SQL database to another SQL
Server. The way I do it isl iek this: I make a backup of the database on the
original server, copy it to thje new server, make there a new database, and
restore the backup in that new database.
So far no problem, but when I want to put put the logins in it I'm getting
trouble:
I want to make on the new server a Login with the same name and password: so
I go to the Security\Logins\, and create on. But when I want to assign that
Login to the SPecific Database (on the Database Access-tab), I got an error
which is saying that that there is alreaddy a user or role with that name
for that database.
When I go look in the Databases\MyDataBase\Users\ there isn't any user in
it...
Anybody knows how to resolve this very annoying problem? Is there a way to
backup/copy the Logins and Users?
Maybe I do something wrong when creating my users?
I create them like this: I go to the Security\Logins\ and assign them there
to the database (on the Database Access-tab). Than I go to the
Databases\MyDataBase\Users\ and add them there: New -> Database User...
Anybody got any idea? I woudl really appreciate some help here.
Thanks a lot,
Pieterhi Pieter,
"DraguVaso" <pietercoucke@.hotmail.com> ha scritto nel messaggio
news:4007da7e$0$311$ba620e4c@.news.skynet.be...

quote:

---

> Hi,
> I'm always having problems when I want to move a SQL database to another

---

SQL

quote:

---

> Server. The way I do it isl iek this: I make a backup of the database on

---

the

quote:

---

> original server, copy it to thje new server, make there a new database,

---

and

quote:

---

> restore the backup in that new database.
> So far no problem, but when I want to put put the logins in it I'm getting
> trouble:
> I want to make on the new server a Login with the same name and password:

---

so

quote:

---

> I go to the Security\Logins\, and create on. But when I want to assign

---

that

quote:

---

> Login to the SPecific Database (on the Database Access-tab), I got an

---

error

quote:

---

> which is saying that that there is alreaddy a user or role with that name
> for that database.
> When I go look in the Databases\MyDataBase\Users\ there isn't any user in
> it...
> Anybody knows how to resolve this very annoying problem? Is there a way to
> backup/copy the Logins and Users?
>
> Maybe I do something wrong when creating my users?
> I create them like this: I go to the Security\Logins\ and assign them

---

there

quote:

---

> to the database (on the Database Access-tab). Than I go to the
> Databases\MyDataBase\Users\ and add them there: New -> Database User...
> Anybody got any idea? I woudl really appreciate some help here.
> Thanks a lot,
> Pieter
>

---

this problem is known as "orphaned users"...
the "imported" database already hase users that are otu of sync with the
corrisponding Login objects, that's to say there's not a corresponding login
for the specified users or the sid do not match in the JOIN sid.sysusers =
sid.syslogins relation, with NULL value result...
you have to fix them using system stored procedure sp_change_users_login
and/or drop the orphan users...
please have a look at
http://www.sqlservercentral.com/col...rokenlogins.asp
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/?id=246133
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/?id=168001
hth
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.5.0 - DbaMgr ver 0.51.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Thanks a lot! I'm gonna take a look at that :-)
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:bu8mr9$equ58$1@.ID-207518.news.uni-berlin.de...

quote:

---

> hi Pieter,
> "DraguVaso" <pietercoucke@.hotmail.com> ha scritto nel messaggio
> news:4007da7e$0$311$ba620e4c@.news.skynet.be...
> SQL
> the
> and
getting[QUOTE]
password:[QUOTE]
> so
> that
> error
name[QUOTE]
in[QUOTE]
to[QUOTE]
> there
> this problem is known as "orphaned users"...
> the "imported" database already hase users that are otu of sync with the
> corrisponding Login objects, that's to say there's not a corresponding

---

login

quote:

---

> for the specified users or the sid do not match in the JOIN sid.sysusers =
> sid.syslogins relation, with NULL value result...
> you have to fix them using system stored procedure sp_change_users_login
> and/or drop the orphan users...
> please have a look at
> http://www.sqlservercentral.com/col...rokenlogins.asp
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL

---

Server

quote:

---

> http://support.microsoft.com/?id=314546
> 224071 INF: Moving SQL Server Databases to a New Location with

---

Detach/Attach

quote:

---

> http://support.microsoft.com/?id=224071
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> 246133 INF: How To Transfer Logins and Passwords Between SQL Servers
> http://support.microsoft.com/?id=246133
> 168001 PRB: User Logon and/or Permission Errors After Restoring Dump
> http://support.microsoft.com/?id=168001
> hth
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.5.0 - DbaMgr ver 0.51.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

---

|||I use the following statements to create a script to run on the new server.
Note that this is tested for SQL2000 only.
--For SQL logins
SELECT 'sp_addlogin '''+
left(name + ''', ',15), --Adjust for max login length
password,
+ ', ''' +
db_name(dbid) +
+ ''', ''' +
+ language
+ ''', ',
sid,
', skip_encryption'
FROM master.dbo.sysxlogins
where srvid is null
and xstatus&4 <> 4 --isntname
order by name
--For Windows logins
SELECT 'sp_grantlogin '''+ name + ''''
FROM master.dbo.sysxlogins
where srvid is null
and xstatus&4 = 4 --isntname
and name <> 'BUILTIN\ADMINISTRATORS'
order by name
HTH,
Mike Kruchten
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:4007da7e$0$311$ba620e4c@.news.skynet.be...

quote:

---

> Hi,
> I'm always having problems when I want to move a SQL database to another

---

SQL

quote:

---

> Server. The way I do it isl iek this: I make a backup of the database on

---

the

quote:

---

> original server, copy it to thje new server, make there a new database,

---

and

quote:

---

> restore the backup in that new database.
> So far no problem, but when I want to put put the logins in it I'm getting
> trouble:
> I want to make on the new server a Login with the same name and password:

---

so

quote:

---

> I go to the Security\Logins\, and create on. But when I want to assign

---

that

quote:

---

> Login to the SPecific Database (on the Database Access-tab), I got an

---

error

quote:

---

> which is saying that that there is alreaddy a user or role with that name
> for that database.
> When I go look in the Databases\MyDataBase\Users\ there isn't any user in
> it...
> Anybody knows how to resolve this very annoying problem? Is there a way to
> backup/copy the Logins and Users?
>
> Maybe I do something wrong when creating my users?
> I create them like this: I go to the Security\Logins\ and assign them

---

there

quote:

---

> to the database (on the Database Access-tab). Than I go to the
> Databases\MyDataBase\Users\ and add them there: New -> Database User...
> Anybody got any idea? I woudl really appreciate some help here.
> Thanks a lot,
> Pieter
>
>

---

Login/User modification from an application?

I have an application and I want to add/edit/del Logins and Users in Sql
Server 2005 through the application, can I do it? I am using windows
authentication. Currently I call a stored proc that I try to pass a login
and it doesn't work. For example this is my Delete.
ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
-- Add the parameters for the stored procedure here
@.LoginName nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
Begin
--Drop the login which drops the user?
Drop Login [@.LoginName]
End
END
When it runs I get you can't add a login called @.LoginName. If I take the
brackets off it will not compile. Can i do what I'm trying to do? I've als
o
tried using sp_DropLogin w/o success. I did see somewhere in documentation
for the sp_dropLogin "sp_droplogin cannot be executed within a user-defined
transaction." There is nothing about the in the Drop Login documentation.
Is this the problem?
Thanks in advance,
Greg P.Just to follow up, mainly what I'm trying to do is used passed parameters in
my add/alter/drop statements. Is there a way to have the @.LoginName
evaluated before the the whole line is evaluated?
"Greg P." wrote:

> I have an application and I want to add/edit/del Logins and Users in Sql
> Server 2005 through the application, can I do it? I am using windows
> authentication. Currently I call a stored proc that I try to pass a login
> and it doesn't work. For example this is my Delete.
> ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
> -- Add the parameters for the stored procedure here
> @.LoginName nvarchar(50)
> AS
> BEGIN
> SET NOCOUNT ON;
> Begin
> --Drop the login which drops the user?
> Drop Login [@.LoginName]
> End
> END
> When it runs I get you can't add a login called @.LoginName. If I take the
> brackets off it will not compile. Can i do what I'm trying to do? I've a
lso
> tried using sp_DropLogin w/o success. I did see somewhere in documentatio
n
> for the sp_dropLogin "sp_droplogin cannot be executed within a user-define
d
> transaction." There is nothing about the in the Drop Login documentation.
> Is this the problem?
> Thanks in advance,
> Greg P.|||No...it's because DROP LOGIN itself doesn't accept
parameters and that's what you are asking it to do. You
would need to use dynamic SQL but you really would want to
read the following first:
http://www.sommarskog.se/dynamic_sql.html
So for the stored procedure you posted to work, one option
would be to dynamically build the statement and pass it into
an EXEC.
So instead of the line:
Drop Login [@.LoginName]
you would use something like:
EXEC('DROP LOGIN ' + @.LoginName)
-Sue
On Mon, 25 Sep 2006 13:31:01 -0700, Greg P.
<GregP@.discussions.microsoft.com> wrote:

>I have an application and I want to add/edit/del Logins and Users in Sql
>Server 2005 through the application, can I do it? I am using windows
>authentication. Currently I call a stored proc that I try to pass a login
>and it doesn't work. For example this is my Delete.
>ALTER PROCEDURE [dbo].[usp_UserRoleDelete]
> -- Add the parameters for the stored procedure here
> @.LoginName nvarchar(50)
>AS
>BEGIN
> SET NOCOUNT ON;
> Begin
> --Drop the login which drops the user?
> Drop Login [@.LoginName]
> End
>END
>When it runs I get you can't add a login called @.LoginName. If I take the
>brackets off it will not compile. Can i do what I'm trying to do? I've al
so
>tried using sp_DropLogin w/o success. I did see somewhere in documentation
>for the sp_dropLogin "sp_droplogin cannot be executed within a user-defined
>transaction." There is nothing about the in the Drop Login documentation.
>Is this the problem?
>Thanks in advance,
>Greg P.

Login problems with SQL Express edition.

Hi,

when running my application and trying to access an SqlServer express edition DB, I encounter the folowing error:

Cannot open user default database. Login failed.

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Does any one know how this problem can be resolved?

It is the SQL Server that comes togather with the VS2005.

Thank you!

The problem doesnt occure when i run the application using the default web server (cassini). It only occures when I run it using IIS.

But how can i run the application on IIS and use the DB?

Thnaks.

Login problem

This is probably all due to my limited understanding of SQL Server security,
so any direction will be greatly appreciated.
Using Win2K, SQLServer 7, and VB.NET 2005..
My app requires a user to login to the database. Since the PCs running the
app are shared, I can't use the current logged in user so the app has a
login screen. The idea is to use the same uid and pwd used to login to their
Windows account. SQL Server is set up to use mixed authentication. So the
user logged in to the PC is Dave. Now Steve sits down to run the app and
logins in through the app's login screen. The app builds a connect string:
Server=server1;Database=homedb;User
ID=DOM1\Steve;Password=stevepwd;Integrat
ed Security=SSPI
This always connects as the current user logged in to the PC. If I remove
the Integrated Security token, the login fails because the SQL Server login
DOM1\Steve does not exist.
What I'm after is letting SQL Server validate the user against an existing
Windows login which may or may not be the user currently logged in to the
machine. Is there any way to do this?
Thanks..
Michael WhiteMicheal,
What you are asking is possible, but difficult. In your connection string,
you would not want to use a UID parameter, that is for SQL Server
authentication. If you were using SQL Server 2005, you might be able to get
by with using the EXECUTE AS clause, but that's not an option here. So you
will need to write some code that impersonates the user who is logging into
the database. I'm assuming that since you mention VB.NET 2005, you are using
the .Net Framework 2.0 to code against. Look in MSDN -> .Net Development ->
.Net Framework SDK -> .Net Framework -> programming with the .Net Framework
-> Securing Applications -> Role-Based Security -> Principal and Identity
Objects -> Impersonating and Reverting.
That article is about doing what you ask, only from the standpoint of a web
page. But to do what you want, the tasks will be the same. If you think it's
something you want to tackle, let me know and I can drop some code...
Dave
"Michael White" wrote:

> This is probably all due to my limited understanding of SQL Server securit
y,
> so any direction will be greatly appreciated.
> Using Win2K, SQLServer 7, and VB.NET 2005..
> My app requires a user to login to the database. Since the PCs running the
> app are shared, I can't use the current logged in user so the app has a
> login screen. The idea is to use the same uid and pwd used to login to the
ir
> Windows account. SQL Server is set up to use mixed authentication. So the
> user logged in to the PC is Dave. Now Steve sits down to run the app and
> logins in through the app's login screen. The app builds a connect string:
> Server=server1;Database=homedb;User
> ID=DOM1\Steve;Password=stevepwd;Integrat
ed Security=SSPI
> This always connects as the current user logged in to the PC. If I remove
> the Integrated Security token, the login fails because the SQL Server logi
n
> DOM1\Steve does not exist.
> What I'm after is letting SQL Server validate the user against an existing
> Windows login which may or may not be the user currently logged in to the
> machine. Is there any way to do this?
> Thanks..
> Michael White
>
>|||Dave..
Many thanks.. that MSDN article was exactly what I needed. I have it working
like a charm! Thanks again
Michael
"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:DCAFCEAC-6C67-497A-B940-1290B5456B4D@.microsoft.com...[vbcol=seagreen]
> Micheal,
> What you are asking is possible, but difficult. In your connection string,
> you would not want to use a UID parameter, that is for SQL Server
> authentication. If you were using SQL Server 2005, you might be able to
> get
> by with using the EXECUTE AS clause, but that's not an option here. So you
> will need to write some code that impersonates the user who is logging
> into
> the database. I'm assuming that since you mention VB.NET 2005, you are
> using
> the .Net Framework 2.0 to code against. Look in MSDN -> .Net
> Development ->
> .Net Framework SDK -> .Net Framework -> programming with the .Net
> Framework
> -> Securing Applications -> Role-Based Security -> Principal and Identity
> Objects -> Impersonating and Reverting.
> That article is about doing what you ask, only from the standpoint of a
> web
> page. But to do what you want, the tasks will be the same. If you think
> it's
> something you want to tackle, let me know and I can drop some code...
> Dave
> "Michael White" wrote:
>