Recently I was working with some database restoring and configuring stuff. And I was getting errors due to the conflicts on database users, server logins and their permissions.
In the ideal scenario, there should be a 'login' in the database server, and a corresponding 'user' for the database. Sometimes people get confused with the difference of these two. But they are for two entirely different purposes.
Restored database contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This situation is usually is known as having "orphaned users". Following steps will be able to resolve this situation.
In above the first step (creating the login), TestDB is the default database for the login. Which is the first database login connected after logging in.
The converse of the above described scenario; if no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server.
But there’s a single exception to this. Even if there is no user account is created in a database for a particular login, still the login can access the database trough a “guest user”. This guest user is created automatically for each database when it was created. And it is set to disabled by default. Another important thing to mention is, this guest login cannot be deleted from the database.
You can enable and disable that account using the below query.
However it is not recommended for giving the sensitive database permissions for guest user. I found some articles on the web (link) where, people have published about possible security risks/bugs in using the guest user and granting permissions on it.
In the ideal scenario, there should be a 'login' in the database server, and a corresponding 'user' for the database. Sometimes people get confused with the difference of these two. But they are for two entirely different purposes.
- Login - Used for user authentication, and created in the server level
- User (Database user) – Provide the database access and used for permission validations on the data base. Created for each database specifically.
Restored database contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This situation is usually is known as having "orphaned users". Following steps will be able to resolve this situation.
-- Creating the login Use master sp_addlogin 'test', 'password', 'TestDB' -- Granting the access to the created loginUse TestDB Use TestDB sp_grantdbaccess 'test' -- Resolve orphaned users Use TestDB sp_change_users_login 'update_one', 'test', 'login_pswd'
In above the first step (creating the login), TestDB is the default database for the login. Which is the first database login connected after logging in.
The converse of the above described scenario; if no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server.
But there’s a single exception to this. Even if there is no user account is created in a database for a particular login, still the login can access the database trough a “guest user”. This guest user is created automatically for each database when it was created. And it is set to disabled by default. Another important thing to mention is, this guest login cannot be deleted from the database.
You can enable and disable that account using the below query.
-- To give access to guest account from SA login: USE TEST_TABLE GRANT CONNECT TO GUEST -- To revoke access to guest account from SA login: USE TEST_TABLE REVOKE CONNECT FROM GUEST -- To grant permission on tables for guest user GRANT SELECT ON TestDB.EmloyeeDetails TO GUESTGranting the permission can be also done with using the SQL server Management Studio through it’s GUI. Now try a SELECT query on the database table, with a login which its database user is not created in the TestDB database. You should be able to perform the operation even without having the database user name.
However it is not recommended for giving the sensitive database permissions for guest user. I found some articles on the web (link) where, people have published about possible security risks/bugs in using the guest user and granting permissions on it.
No comments:
Post a Comment
Had to enable word verification due to number of spam comments received. Sorry for the inconvenience caused.