MS SQL Server - xp_logininfo in SQL 2005

Author

xp_logininfo in SQL 2005

Michael Viking

2006-10-24, 6:37 pm

Greetings!

Our software depends on customers being able to give users access to
xp_logininfo. Now with SQL 2005 we have the wonderful
" In earlier versions of Microsoft SQL Server, permission to execute
xp_logininfo can be granted to users. In SQL Server 2005, xp_logininfo can
be run only by an administrator."

Obviously we can't make our users administrators!!

This is very bad for us, and it doesn't seem like there's really a
workaround. Any ideas? Any workarounds? Why did MS do this?

Thanks,
-Michael Viking

Kalen Delaney

2006-10-24, 6:37 pm

Hi Michael

Another wonderful new feature of SQL Server 2005 is the ability to create a
procedure that can EXECUTE AS any other user. So you can create a procedure
to run xp_logininfo, and whoever runs your procedure will be able to run
xp_logininfo.

--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com

"Michael Viking" <TheViking@sweden.se> wrote in message
news:OUruQdu7GHA.1248@TK2MSFTNGP03.phx.gbl...
> Greetings!
>
> Our software depends on customers being able to give users access to
> xp_logininfo. Now with SQL 2005 we have the wonderful
> " In earlier versions of Microsoft SQL Server, permission to execute
> xp_logininfo can be granted to users. In SQL Server 2005, xp_logininfo can
> be run only by an administrator."
>
> Obviously we can't make our users administrators!!
>
> This is very bad for us, and it doesn't seem like there's really a
> workaround. Any ideas? Any workarounds? Why did MS do this?
>
> Thanks,
> -Michael Viking
>
>

Michael Viking

2006-10-24, 6:37 pm

Hi Kalen,

Thanks for the quick response. I'm a little confused by your solution. We
have a customer base that's about 7 years large, using various versions of
our software. How does this new SQL 2005 feature help our current customers
if they upgrade to SQL 2005 (as some have)? I guess your workaround
involves re-writing many versions of our code and distributing it to our
customers?

Don't get me wrong, I appreciate the idea and it might be the way to go for
newer versions, but I don't see how we can get it to our current customers.
And if it's new for SQL 2005, then we have to have multiple code paths
depending on what kind of server we're connected to (which is poor
programming). And I can think of no reason to disallow an admin from giving
a user access to run this function. Do you have any insight into why MS did
this?

Thanks,
-Michael

"Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
news:uRG3Hsu7GHA.4708@TK2MSFTNGP05.phx.gbl...
> Hi Michael
>
> Another wonderful new feature of SQL Server 2005 is the ability to create
a
> procedure that can EXECUTE AS any other user. So you can create a
procedure
> to run xp_logininfo, and whoever runs your procedure will be able to run
> xp_logininfo.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
>
> "Michael Viking" <TheViking@sweden.se> wrote in message
> news:OUruQdu7GHA.1248@TK2MSFTNGP03.phx.gbl...
can[color=darkred]
>
>

Michael Viking

2006-10-24, 6:37 pm

Also, I'm a little concerned that your solution sets up a nice security
hole, but I'm no entirely sure. Does it?

Thanks,
-Michael

"Michael Viking" <TheViking@sweden.se> wrote in message
news:uajWw6u7GHA.1188@TK2MSFTNGP05.phx.gbl...
> Hi Kalen,
>
> Thanks for the quick response. I'm a little confused by your solution.
We
> have a customer base that's about 7 years large, using various versions of
> our software. How does this new SQL 2005 feature help our current
customers
> if they upgrade to SQL 2005 (as some have)? I guess your workaround
> involves re-writing many versions of our code and distributing it to our
> customers?
>
> Don't get me wrong, I appreciate the idea and it might be the way to go
for
> newer versions, but I don't see how we can get it to our current
customers.
> And if it's new for SQL 2005, then we have to have multiple code paths
> depending on what kind of server we're connected to (which is poor
> programming). And I can think of no reason to disallow an admin from
giving
> a user access to run this function. Do you have any insight into why MS
did
> this?
>
> Thanks,
> -Michael
>
>
> "Kalen Delaney" < replies@public_newsg
roups.com> wrote in message
> news:uRG3Hsu7GHA.4708@TK2MSFTNGP05.phx.gbl...
create[color=darkred
]
> a
> procedure
> can
>
>

Tibor Karaszi

2006-10-24, 6:37 pm

Kalen's solution as well as a couple of alternatives are discussed at
http://www.sommarskog.se/grantperm.html

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Michael Viking" <TheViking@sweden.se> wrote in message
news:umuDK$u7GHA.4500@TK2MSFTNGP02.phx.gbl...
> Also, I'm a little concerned that your solution sets up a nice security
> hole, but I'm no entirely sure. Does it?
>
> Thanks,
> -Michael
>
> "Michael Viking" <TheViking@sweden.se> wrote in message
> news:uajWw6u7GHA.1188@TK2MSFTNGP05.phx.gbl...
> We
> customers
> for
> customers.
> giving
> did
> create
>
>

Sponsored Links