March 16, 2016 at 11:03 am
Hi all,
I understand the difference between a login (at instance level) and a user at database level.
I have also tried out that a login and be mapped to multiple users from different database.
I have also tried that a username can only be mapped to 1 login.
But I have no idea what is the benefit of having the login / separated from the database.
q1) is it so that 1 login can be use to access multiple database and also database can be "migrated" to another instance with a different set of logins (just to mapped them back to the users) ? is that all ?
q2) when creating a new login, if i set the default database to master, even though there is no corresponding user in the masterdb, i will be login to login as guest user.
however, when setting the default database to any other userdb, if there is not a corresponding user in the userdb mapped to the login, the login will fail. But there is a guest account in the userdb also. Why can't we login as guest for userdb ?
q3) Lastly, how do we check if a privilege is a instance privilege or a database privilege ?
for e.g. if i want to backup database, do i need to have the privilege/role for the database user, or i need to have instance privilege/role for the login ?
That's all folk. ! Thanks for all the replies!
Regards,
Noob
March 17, 2016 at 6:51 am
Hi all,
Anyone ;(
Regards,
Noob
March 17, 2016 at 7:05 am
q1) I would say the reason is atomicity of the data plus permissions, and yes, portability. the permissions related to the database are contained in the database itself.
backups and restores have both data and the access to the point in time of the database backup.
logins point to the user in the database, and is a small piece of info that is outside of the database in question (unless we talk about contained databases); since it's just a pointer to the user, it's easily fixed.
if the user in the db is a domain account(mydomain\lowell), if i restore the database on a different server, i have to just add that login.
if the user in the db is a SQL account, just adding it as a user is not enough, because the underlying SID associated to the user will be different/unique, and you have to fix that. the best way is to script the sql login on the original server, and runt he script on the new server so the sids match.
then every restore re-establishes the access and permissions correctly.
for q2, I would say every login gets limited access to master and tempdb, because there are default permissions in the public role they inherit just by being created. changing the default db is an option, as you give more permissions to other databases, but it's a great default baseline to make it master.
for q3, the only instance permission to check for is if the user is a sysadmin; (select IS_SRVROLEMEMBER('sysadmin','mydomain\lowell') )
if the login is sysadmin, the permissions in a database don't matter, the login is an unrestricted user in all databases.
if the login is not sysadmin, the permissions in each database are the limitation.
szejiekoh (3/16/2016)
Hi all,I understand the difference between a login (at instance level) and a user at database level.
I have also tried out that a login and be mapped to multiple users from different database.
I have also tried that a username can only be mapped to 1 login.
But I have no idea what is the benefit of having the login / separated from the database.
q1) is it so that 1 login can be use to access multiple database and also database can be "migrated" to another instance with a different set of logins (just to mapped them back to the users) ? is that all ?
q2) when creating a new login, if i set the default database to master, even though there is no corresponding user in the masterdb, i will be login to login as guest user.
however, when setting the default database to any other userdb, if there is not a corresponding user in the userdb mapped to the login, the login will fail. But there is a guest account in the userdb also. Why can't we login as guest for userdb ?
q3) Lastly, how do we check if a privilege is a instance privilege or a database privilege ?
for e.g. if i want to backup database, do i need to have the privilege/role for the database user, or i need to have instance privilege/role for the login ?
That's all folk. ! Thanks for all the replies!
Regards,
Noob
Lowell
March 17, 2016 at 7:21 am
A login is for Authentication.
A User is for Authorization.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply