| Author |
Topic |
|
dmaxj
Posting Yak Master
174 Posts |
 Posted - 2010-04-09 : 10:04:53
|
| Another DBA and I have an issue regarding give access databases for users.The question:Does a user or group have to exist as an object in Security.Logins before being able to have access to any database on the same server?Regards |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-09 : 10:24:36
|
| Logins are users are not the same thing. Logins are a server level security object. They determine who can connect to the server, and what server level permissions they have. Users are a database level object. A User is a link between a login, and a database, and controls the permissions for that login, on that database. So no, the use will not be able to connect to a database without the login.Try this. Create a SQL Server login called john, and add john as a user of the AdventureWorks Database. Grant john some select permissions on some tables. Login to SSMS as john and select from those tables. All should work well. Now, logout as john, and login as an admin. Delete the Server level login for john. It will warn you that this will not delete the database level user john from any databases. Check the AdventureWorks Users, and you'll see that john is still there. The login is gone, but the user is still there. Now try and connect to the AdventureWorks database as john. You can't, because there is no login for the user john. Hope this helps settle your dispute.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2010-04-09 : 10:34:54
|
| Yes, it does. I was correct... I explained to him that database users could not be added unless the user exists in Logins. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-04-09 : 10:47:22
|
| well, you CAN create database users without logins.see here: http://msdn.microsoft.com/en-us/library/ms173463.aspx |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-09 : 11:17:52
|
quote:
Originally posted by russell
well, you CAN create database users without logins.see here: http://msdn.microsoft.com/en-us/library/ms173463.aspx
Yes, but as far as I know, the user still requires a login to access the database.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-04-09 : 11:44:27
|
| when we are talking about human users, yes.but when we are distinguishing between server principals and database principals (logins and users) it is possible to create users without login. this would typically be used by applications and not by humans |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-09 : 17:38:30
|
quote:
Originally posted by russell
when we are talking about human users, yes.but when we are distinguishing between server principals and database principals (logins and users) it is possible to create users without login. this would typically be used by applications and not by humans
What's the difference? How do you authenticate a user without a login? And how would the differ depending on whether it's an application or a human?------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-04-09 : 19:42:24
|
| you don't authenticate a user without a login.but you can execute as a user without a login among other things |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2010-04-12 : 08:36:35
|
| This is interesting because we managed to remove the AD group from LOGINS, but were able to still add the AD group to the security object of the database. End users were still able to access the database. So, I stand corrected. As stated above, users are using an application (Report Server) to access the database. I guess that I learned something new. So, yes to Russell, the application is executing as a user.Regards |
|
|
|
|
Logins dispute