SQL Server Performance Forum – Threads Archive

Os level permission requirements for xp_logininfo

Hi
I have an application that attempts to display information about Windows accounts associated with a SQL 2000 login. It uses DMO and under the covers is calling xp_logininfo. Public has been granted execute on xp_logininfo. If I run xp_logininfo as a user that is a member of Users, Domain Users and Remote Desktop Users, I get ODBC error 8198 state 24, which according to MS KB article 834124 http://support.microsoft.com/?kbid=834124) is ‘Access Denied’. I can reproduce this by running it via Query Analyzer. The syntax being used is exec master..xp_logininfo ‘DomainUser’, ‘All’ What I would like to find out is what is the minimum level of permission a Windows user must have in order for this to return a result? —
Regards
Craig

According to BOL:

quote:Execute permissions for xp_logininfo default to members of the db_owner fixed database role in the master database and members of the sysadmin fixed server role, but can be granted to other users.

With all (extended) stored procedures, the login must have execute permissions on the procedure itself.

.. also keep in mind only SYSADMIN privileged user can execute few extended stored procedures, but on a security aspect this is not advisable to open this loophole for everyone, restrict it to DBAs only. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the replies. As mentioned, I have granted execute to Public. Once I can get it to work I can review the access to this procedure. The question remains; what Windows permission set is required? —
Regards
Craig

quote:Originally posted by cnicho The question remains; what Windows permission set is required?

None, the permissions are granted to the login at the database level, not the ‘Windows’ level. Ensure that the login in question has exec rights on the xp_logininfo procedure.

Also, the information retrieved by xp_logininfo is purely SQL Server-centered. According to BOL:

quote:Reports the account, the type of account, the privilege level of the account, the mapped login name of the account, and the permission path by which an account has access to MicrosoftÂ® SQL Serverâ„¢.

So you don’t need to bother about permissions for Windows, it remains within the scope of the SQL Server isntallation in and by itself.

I have discovered the cause of the error. It was a Windows authentication error. The SQL Server service account did not have enough privilege in the domain. —
Regards
Craig

What specific privilige did the sql account need to run this procedure?

The SQL Server and SQL Agent services need to be run within the context of a user account which is a member of the local Administrators group. I defined a Domain User account and added the account to the local Administrators group on the machine running SQL Server and changed the services to logon using the Domain account. —
Regards
Craig

]]>