Why different views of users in Enterprise Manager?

This is related to another open question below but I thought I would post the related question separately.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21207352.html

I have acquired a new SQL Server to manage and I am seeing a few things I've never noticed before.   One of those things is that if I use Enterprise Manager to view the Users in a given database, I see the whole list of users including what appear to be orphaned users...

Name          Login Name          Database Access
UserA                                    Permit
UserB                                    Permit
UserC         Domain\UserC      Permit

The list I see in EM basically corresponds to what I see in that database's sysusers table (excluding db roles and so on).  However, I just went to see someone on the Help Desk who appears to access this SQL Server via 'BuiltIn\Administrators' as that Help Desk user is a Domain admin.   However, on his desktop running Enterprise Manager, I could see that viewing users in the same database only lists users that have a corresponding login...

Name          Login Name          Database Access
UserC         Domain\UserC      Permit

Any ideas why this is?

I think the answer is to do with the way Windows accounts can be granted to databases.   This server is 'Windows Only' authentication and I've not dealt with one of these before (normally Mixed SQL And Windows).  The issue seems to be that if the following system stored procedure is run, it adds a Windows account to the database WITHOUT adding or requiring  a corresponding Login at the server level...  I didn't think this was possible as I thought there always had to be a corresponding Login?

User DatabaseA
sp_GrantDBAccess 'domain\UserA', 'UserA'

(By the way, the following is used to add UserA into a database role that has the object perms...
sp_AddRoleMember 'dbRole', 'UserA')

Afterwards, if I look in EM at the database's users, I see this...

Name          Login Name          Database Access
UserA                                    Permit

Although at first sight it appears to be orphaned, the point is that "sp_GrantDBAccess 'domain\UserA', 'UserA'" does work - it creates the user in the database and does appear to give access to the corresponding Windows account BUT no corresponding Login is present on the server.  I didn't know this was possible.  Is it?

AustinSeven


LVL 10
AustinSevenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
Because syslogins and sysusers are in different dbs (ie: syslogins is in the master db only) and sysusers sits in each db.   The system stored procs for adding entries are independent of eachother


What you should do when you want a new person eg     simpsons\bart

is first as the login, then at the user. eg:

EXEC sp_grantlogin 'simpsons\bart'
GO
USE MyDatabase
GO
EXEC sp_grantdbaccess 'simpsons\bart','bart'


Hops this makes sense.

PS there is a section in BOL on "Troubleshooting Orphan Users" is you need it.
0
AustinSevenAuthor Commented:
ShogunWade,  Yes you're right and I was aware of those issues.  I've always worked on SQL Server in the way you describe... Adding a login and then linking it to the user in the db.   What's puzzled me is that it appears possible to avoid using sp_grandlogin and go straight to the next step (with Windows authenticated accounts)...

USE MyDatabase
GO
EXEC sp_grantdbaccess 'simpsons\bart','bart'

I've now tested this independently on my desktop's msdb installation.   As I said, it appears like an orphaned user because it doesn't make any reference to the domain login:-

EM users view on  MyDatabase:-
Name                   Login Name          Database Access
bart                                                Permit

However, it is a valid user entry because connections can be made to the database from bart.   I wonder if this is anything to do with Active Directory?

Still puzzled.

AustinSeven
0
ShogunWadeCommented:
"Yes you're right and I was aware of those issues. "  -Sorry for teaching egg sucking.

Yes it is possible to create a user without a login.    However the Bart Simpson shouldnt be able to connect unless he is either part of a group login or has an explicit login.

EG a windows Group    simpsons\family   as a login

and simpsons\bart as a user  .  Bart would be able to gain access to the database via his group membership but his permisions on the database would be dictated by his own user account.

I employ this technique in a number of cases where I want all the simpsons to have generic rights but bart has slightly different ones in a given db.

(Not sure if this answers your question or not)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AustinSevenAuthor Commented:
ShogunWade,

> Yes it is possible to create a user without a login.    However the Bart Simpson shouldnt be able to
>connect unless he is either part of a group login or has an explicit login.

This is ok but I can't find what group login applies (an explicit one does not).   Any ideas how I could trace exactly what group login applies?  

For your information, as a test, I ran the following on my MSDE installation on my desktop...


EXEC sp_GrantDBAccess 'simpsons\bart' , 'bart'

The above created a user in the database.  It  did not complain that there was not a login present (ie.'simpsons\bart' ) and there was no login group there.  Further more, the above user will accept logins.  I can see the same view of users in EM as I described earlier in the thread - ie. looks like an orphaned user but isn't.

Also, any ideas why I get this long view of users in my EM (I can see all these users even if they don't have an explicit login or group listed) but other people also in the System Administrators role around here only get a shorter list that contains only users that have an associated explicit Login or Group?   I also note that where an 'explicit group' is listed, under the 'Database Access' column, it states... 'Via Group Membership'.  However, as I said, with the 'bart' user, it just states 'Permit'.

AustinSeven
0
AustinSevenAuthor Commented:
I'll close this question down soon.  Answers still welcome.

AustinSeven
0
ShogunWadeCommented:
Sorry Austin,  

I lost track of this amongst my emails i must have accidentally marked as read :(.    Let me just read your last reply.
0
ShogunWadeCommented:
"This is ok but I can't find what group login applies (an explicit one does not).   Any ideas how I could trace exactly what group login applies?  "

sp_helpuser might help you trace this but i must confess that ive never tried it in this conxtext.  You may also be able to get some info using the
IS_MEMBER function which returns whether the current user is a member of the specified group.

when you attempted to add the user


EXEC sp_GrantDBAccess 'simpsons\bart' , 'bart'  

should have given you :

Server: Msg 15401, Level 11, State 1, Procedure sp_grantdbaccess, Line 99
Windows NT user or group 'simpsons\bart' not found. Check the name again.

unless there was an implicit group membership in the login.


I just tested this here:

My Domain = simpsons
Windows group = Adults      -- members of this group are Homer

Login created for simpsons\adults

EXEC sp_GrantDBAccess 'simpsons\bart' , 'bart'
failed with the above message.

yet

EXEC sp_GrantDBAccess 'simpsons\homer' , 'homer'
works

One thing you might want to do is to check active directory to identify what groups the problem login is in.









0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AustinSevenAuthor Commented:
hello again,

At the beginning of the week, I had a test user created in Active Directory just the same as the users are normally created for the system in question.   The Help Desk did it for me, following their standard process.   I was interested to know what groups the test user (eg. bart) would included in...  First was 'Domain Users' (which I suppose must be standard) and the other AD group was a SQL related one... It's called 'SQLUsers'.    Behind all of this I was and remain puzzled why the user was able to be added with no apparent login or group login.  As you said, one would have expected it to error. Anyway, with this information, I went to SQL Server and found the following windows group setup in Security...

domain\SQLUsers

However, when I looked at the above group's login properties in EM, I went straight to the 'Database Access' tab and was very surprised to see that the group login didn't have access to the database in question (say, 'DatabaseA').  ie. the 'Permit' checkbox wasn't ticked for the database.   I also checked if my some chance bart was a System Administrator... but he isn't.  At this point, you would have thought that bart could not access DatabaseA... BUT HE CAN!!!  

Just to recap, the test user is listed in Users in DatabaseA:-

Name                   Login Name          Database Access
bart                                                Permit

As a point of information which I am not sure is important to this discussion, as part of the standard user creation procedures, bart is added to a database role in DatabaseA with the same name as the Windows group - ie. 'SQLUsers'.
Of course, it is this database role that defines the object permissions applicable to bart in DatabaseA.    The only thing is, I am still stuck for a clue as to how bart is gaining access to DatabaseA.   I need putting out of my mysery.

If that wasn't enough... out of this problem I have found another puzzle.   In my Enterprise Manager session, if I select DatabaseA and then select 'Users', I get a long list of all users... Most of these users are like bart because they appear like orphaned users but do have 'Permit' under the 'Database Access' column.   A smaller proportion of the users are more conventional - they have a corresponding Login.    On my colleagues' Enterprise Manager sessions, they only generally see the shorter list of users - ie. not the users where the Login Name is blank.   Why?  It's not so important I guess but it's a loose end and I hate those!

AustinSeven



0
ShogunWadeCommented:
ok.  I think i see now,   I'll try and explain (pls let me know if my explanation is confusing at all)...  Ill include a bit of what you say to get it all in context....(ill use the domain name = simpsons)

Help desk set up BART in active directory as a user.   He is in "Domain Users" (which you correctly assumed  as the list of all domain users, ie: participation of this group is mandatory).   Bart is also in the simpsons\SQLUsers windows group.

In the SQL Logins simpsons\SqlUsers is a login but doesnt have explicit access to a dabases (nor does it have explicit denial to a database).

I would guess also (but it is not really significant) that the default database for simpsons\SQLUsers is "master".

At this point technically simpsons\bart could login to sql server by virtue of being a member of simpsons\sqlusers.  Although he will not have access to DatabaseA  but will have access to master (this is implicit for the workings of sql but his access will be limited)

when you issue

EXEC sp_GrantDBAccess 'simpsons\bart' , 'bart'
sql server will validate the login simpsons\bart this will return that he is a valid login (via simpsons\sqlusers)  and thus create a user in DatabaseA called simpsons\bart.

At this point I dont believe that enterprise manager's interface is clear enough to identify the relationship properly because EM doesnt actually know who is a member of simpsons\sqlusers, it leaves that detail to windows (or more specifcally active directory).  

So EM will still show simpsons\sqlusers as not having a tick for access to DatabaseA.

Bart can still connect the SQL via membership to the simpsons\sqlusers windows group, but he will also be able to access DatabaseA because he has an explicit user name there.  (though SQL EM will not be able to show this fact).



As for " On my colleagues' Enterprise Manager sessions, they only generally see the shorter list of users - ie. not the users where the Login Name is blank. ",   Im afraid i have absolutely no idea on that one :(  Its not something ive seem before or can duplicate here.

0
AustinSevenAuthor Commented:
ShogunWade,

Thanks, I understood your explanation very well.   You're right.  Simpsons\sqlusers does have Master as the default db.   I think I was just butting up against the red-herring that was the simpsons\sqlusers lack of explicit database access (according to EM) to DatabaseA.  As you inferred, this seems to be partly a case of security views falling between the gaps between the 'Windows' and 'SQL Server' floorboards.   I think it would have been tidier if MS had just stopped it from happening - enforcing the explicent Login or Login Group in all cases.  

I suspect that, as other databases on this server have simpsons\sqlusers set as having database access, DatabaseA was just an oversight and it was never picked up because it carried on working... and confused the heck out of me as a result!   One action might be for me to set simpsons\sqlusers to have explicit access to DatabaseA to tidy things up a little.

Thanks again,

AustinSeven  
0
ShogunWadeCommented:
Certainly if all people in simpsons\sqlusers need access that is far the easiest way.

What I tend to do is to have multiple windows groups each which has different permissions then users can be added to multiple windows groups to give then the appropriate permissions.

"Thanks again,"     As always, you are more than welcome .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.