Hello Folks,
You must have seen my previous article-post, which deals with “How to add a new Windows Login” using a Windows Authentication Mode in SQL Server.
If you wish to refer it again, then just browse the link;
Well, in this article I would like to deal with-
“How to Remove a Windows Login”
“How to Deny a Windows Login”
“How to set default database”
“How to treat Orphaned Users”.Here, we go;
How to Remove a Windows Login?
There are two ways, using either of which we can remove a Windows Login:
- Using Management Studio
- With T-SQL
Using Management Studio:
You can easily remove a windows login by right-clicking the context menu to delete the user. It will only removes the user from the SQL Server, but will remain sustain there in the Windows. You can see this:
Then the “Delete Object” window will flash-up and you can select Ok to remove the login.
With T-SQL:
With the revoke stored procedure, you will be able to remove the Windows User from SQL Server, but not from the machine. The code is:
EXEC sp_revokelogin 'Piyush-PC\SQLServerGeeks'
How to Deny a Windows Login?
- The Deny can prevent users or groups from accessing SQL Server even if they could otherwise get an entry from another method.
- This will become much clear through an example:
Let suppose, Piyush is a member of Class A and Class B. It had been declared that Class A granted a Normal Login access, while a Class B granted a Denied access. Since deny overrides grant, so even if he is granted an access from Class A, the Class B group denied access blocks Piyush from accessing to SQL Server.
Therefore, to deny the windows user or group from accessing the SQL Server, just call the stored procedure:
EXEC sp_denylogin 'Piyush-PC\SQLServerGeeks'
If the user or group which has been denied access doesn’t really exist, so the stored procedure above will recreate that login and then denies the user.
How to set the Default Database?
You can see and manage the default in the Login Properties form in General page. Well, you can also set this with the help of stored procedure:
EXEC sp_defaultdb 'Piyush-PC\SQLServerGeeks', 'TEST'
So you have changed the database for SQLServerGeeks login to TEST database.
How to treat with Orphaned Windows Users?
- Orphaned: When a Windows user is added to SQL Server and then removed the Windows domain, without being first removed from the SQL Server. So, the user which still exists in SQL Server is being considered as Orphaned.
- So it means that even if the SQL Server contains the Windows account which does not exist any longer in the domain, it won’t be able to access it.
- Now, if the Windows account be recreated with the same name, it would contain SID and GUID different from the previous one.
- System stored procedure “sp_validatelogins” finds all orphaned users and returns their Windows NT Security Identifiers.
For e.g., “SQLPiyush” was granted permission to access the database, but since it was accidently deleted from the Windows domain. So the query to find this user in the SQL Server is:
EXEC sp_validatelogins
The result can be seen as:
- Therefore, without a Windows Login with a matching SID, the user can’t log into SQL Server.
- To overcome with this problem, the Windows Login had to be removed from SQL Server using the method described previously.
Well, this was all about for this article-post.
Hope you got it understood well 🙂
And also comments on this!!
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
