1

I have noticed that when I open SQL server enterprise manager and I navigate to the list of users under a specific DB, I found users with"Name" but some have no "Login Name" value. What does this means? Does it means that the

1)users cannot log into the server at all?

2)its a system ID(or application ID so no user will be able to use it to log on and only applications can use it to access the database?)

3)there is a single sign on so once the user sign into the server, they can access the db without authentication again?

My second question pertains to the users under the "Security" group in enterprise manager.When lets say "a" is here and "a" is also present in the db users, it means that "a" can access and change data in the db right? But if "b" is in the security group but not in the db users, it means that it cannot log onto the db but only make changes to the server only?

My third question: If default database is master, it means that that user can make changes to all the database?

A huge thanks to anyone who answers my questions!

1 Answer 1

0

Database logins and database users are different. Basically a login is granted permissions the server, a user is granted permissions to database. Logins are mapped to databases.

My guess the reason you have users in a database that aren't mapped to logins is that you've restored a database from another SQL Server and not copied the logins across. There are basically 2 ways of fixing orphaned users. You can use sp_change_users_login, or you can copy the logins between servers so the sid's match.

If you want to use a single sign-on method then you can do this by integrating with Active Directory by creating a windows login instead of using a sql login.

Yes, you can map a login to more than one database. You can GRANT access to different objects for each database to the user, or add the user to a database role. There are also serverwide roles that will give a login access to a database, good example of this is the sysadmin role. Setting a default database for the login doesn't give that login any permissions in the database. You'll find that a login by default is in the public group, and that gives them so very low level access to master & tempdb.

You need to explicity grant access for a login to the database. You do this with sp_adduser. If you create the users in Enterprise Manager then yes, the login name will match the user name, but it doesn't have to, the sproc allows you to set a different username.
This code sample demonstrates the difference:

SELECT USER  --database user
SELECT SYSTEM_USER  --login

I don't know about DB2, but Oracle does not have the concept of login's and user's.

1
  • Hi, if a person has database logins and can also access database as a user, is their ID for the database login and user the same? Is it the same for all types of servers like MSSQL,DB2 and Oracle?
    – Laughy
    Dec 12, 2013 at 15:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .