addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx