AustinSeven
asked on
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.
https://www.experts-exchange.com/questions/21207352/Help-required-on-a-Login-user-access-issue.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
https://www.experts-exchange.com/questions/21207352/Help-required-on-a-Login-user-access-issue.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
ASKER
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
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
"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)
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)
ASKER
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
> 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
ASKER
I'll close this question down soon. Answers still welcome.
AustinSeven
AustinSeven
Sorry Austin,
I lost track of this amongst my emails i must have accidentally marked as read :(. Let me just read your last reply.
I lost track of this amongst my emails i must have accidentally marked as read :(. Let me just read your last reply.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
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
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
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 .
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 .
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.