?
Solved

Why different views of users in Enterprise Manager?

Posted on 2004-11-16
11
Medium Priority
?
264 Views
Last Modified: 2013-12-03
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


0
Comment
Question by:AustinSeven
  • 6
  • 5
11 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12594009
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
 
LVL 10

Author Comment

by:AustinSeven
ID: 12595812
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12596465
"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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 10

Author Comment

by:AustinSeven
ID: 12602147
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
 
LVL 10

Author Comment

by:AustinSeven
ID: 12613177
I'll close this question down soon.  Answers still welcome.

AustinSeven
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12613355
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
 
LVL 18

Accepted Solution

by:
ShogunWade earned 2000 total points
ID: 12613430
"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
 
LVL 10

Author Comment

by:AustinSeven
ID: 12613820
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12614131
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
 
LVL 10

Author Comment

by:AustinSeven
ID: 12614507
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12614957
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question