Re: Windows authentication still prompts for database login

EXEC xp_logininfo 'MyDomain\GroupMemberUserAccount', 'all'
accountname type privilege mapped login name permission path
MyDomain\GroupMemberUserAccount group user MyDomain\GroupMemberUserAccount NULL

I see that the reported type is "group" and the permission path NULL. I would expect the reported type to be "user" and the permission path to be 'MyDomain\WindowsGroupName' if the user account was specified. Did you perhaps specify the group account name instead of the user account name as the first xp_logininfo parameter?

EXEC sp_helplogins 'MyDomain\WindowsGroupName
login name SID DefDBName DefLangName Auser ARemote
MyDomain\GroupMemberUserAccount 0x0.... CCLInformationStore English yes no

login name dbName UserName UserOrAlias
MyDomain\GroupMemberUserAccount MyDBName MyDomain\GroupMemberUserAccount User
MyDomain\GroupMemberUserAccount MyDBName db_datareader MemberOf
MyDomain\GroupMemberUserAccount MyDBName db_datawriter MemberOf

Similarly, I would expect 'MyDomain\WindowsGroupName' to be listed under the login name column instead of the user account name if the group account name was specified.

It's unclear to me how the group is setup in your environment. You might try granting database access with the examples below:

--SQL 2000
EXEC sp_grantlogin 'MyDomain\WindowsGroupName'
USE MyDBName
EXEC sp_grantdbaccess 'MyDomain\WindowsGroupName'
--add role memberships and grant object permissions here
GO

--SQL 2005
CREATE LOGIN [MyDomain\WindowsGroupName] FROM WINDOWS;
USE MyDBName
CREATE USER [MyDomain\WindowsGroupName];
--add role memberships and grant object permissions here

Why does the username get reported as dbo when I'm logged in via
Integrated Security, presumably as MyDomain\Domain Admins?

By default, members of the local Administratiors group (which includes Domain Admins by default) access SQL Server via the BUILTIN\Administrators login. This group is a sysadmin role member and is therefore mapped to the privileged 'dbo' user in all databases.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Ron Porter" <rporter@xxxxxxxxxxxxx> wrote in message news:HPOdnZZPUrFRkPDVnZ2dnUVZ_g2dnZ2d@xxxxxxxxxxxxxxxxx
Thanks, Dan. Here are my results:

EXEC xp_logininfo 'MyDomain\GroupMemberUserAccount', 'all'
accountname type privilege mapped login name permission path
MyDomain\GroupMemberUserAccount group user MyDomain\GroupMemberUserAccount NULL

Check to ensure the Windows Group can access the database as expected:

EXEC sp_helplogins 'MyDomain\WindowsGroupName
login name SID DefDBName DefLangName Auser ARemote
MyDomain\GroupMemberUserAccount 0x0.... CCLInformationStore English yes no

login name dbName UserName UserOrAlias
MyDomain\GroupMemberUserAccount MyDBName MyDomain\GroupMemberUserAccount User
MyDomain\GroupMemberUserAccount MyDBName db_datareader MemberOf
MyDomain\GroupMemberUserAccount MyDBName db_datawriter MemberOf

That looks sensible to me, so I moved on, first to SQL Server 2000
on another box where we have the same problem
If all looks well, you can test security from SSMS or Query Analyzer using
one of the scripts below:

--SQL 2000
USE MyDatabase;
GO
SETUSER 'MyDomain\GroupMemberUserAccount'; -- this line reports "Could not obtain information about Windows NT group/user 'crestlinecoach\domain users'"
GO
SELECT USER_NAME(); -- this line reports "dbo". (keeping in mind that the Execute As failed, so this must be the original context)
GO
--test object permissions here -- not much to do here given that setuser failed
GO
SETUSER;
GO

then to the SQL Server 2005 where the problem was first reported
--SQL 2005
USE MyDatabase;
GO
EXECUTE AS LOGIN = 'MyDomain\GroupMemberUserAccount'; -- this line reports: Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the
principal "MyDomain\GroupMemberUserAccount" does not exist, this type
of principal cannot be impersonated, or you do not have permission.

GO
SELECT USER_NAME(); -- this line reports "dbo" (keeping in mind that the Execute As failed, so this must be the original context)
GO
--test object permissions here -- not too much to do here given that Execute As failed
GO
REVERT;
GO


NOTE: I completely removed and replaced the Domain Users account and
login on our legacy SQL Server 2000 system as a test. The results
reported above did not change, so I didn't do the same on our production
2005 server.

Why does the username get reported as dbo when I'm logged in via
Integrated Security, presumably as MyDomain\Domain Admins?
--
ronp

.

Relevant Pages

  • Re: Global Variables
    ... someone else can answer this - is it the Windows login you are under, ... then the package is run under the context of the account setup ... > permission EM has and the Agent? ...
    (microsoft.public.sqlserver.dts)
  • Re: IT engineer resets password without permission to get access
    ... account have been used. ... login, then they have permission to do it themselves, which means it's ... Sysadmins may hold ultimate power to do whatever we like to anyone's ...
    (uk.legal)
  • XP SP2, FrontPage 2003, WebDAV, Apache/1.3.31 (Unix) DAV/1.0.3
    ... I keep getting "You do not have permission to do this operation ... login with a different account click OK" ... This message repeats and repeats as it appears to walk through the files on ...
    (microsoft.public.frontpage)
  • Re: local authentication problem
    ... I would enabled Logon Failure auditing on the Windows XP box that's hosting ... Then go and look in the Windows Event Log to see what account is being ... > access the page from other computers, I think everyone has permission to ... >> permission for your login. ...
    (microsoft.public.inetserver.iis)
  • Re: FTP site login
    ... Anonymous FTP is not always "less" secure. ... the login, I have to wonder whether XP is trying to log in using the ... currently logged in account and password. ... >> only two accounts that have permission to login. ...
    (microsoft.public.win2000.security)