Sometimes, we’d like to drop a SQL Server Login as a result of we have a tendency to gave associate degree access to someone WHO left, this login was employed by associate degree application that has been decommissioned for instance.
Don’t have a SQL server Management Studio? Don’t worry here’s the link
To do so, we will use SQL Server Management Studio (SSMS) as follows:
- Open SSMS
- Connect to a SQL Server instance
In Object human, head to « Security » node then logins
Right-click on the SQL Server Login you wish to drop then click on “Delete”
5. SSMS can show following warning message
- Click on “OK”
We may additionally execute a DROP LOGIN statement:
DROP LOGIN login_name;
No matter the trail you followed, if everything went OK, your login is currently born. But, as SSMS explicit, you’ll have left a number of things within your databases. If there have been mappings between this login and one or a lot of information users (one per database), these users square measure still there.
We can come back thereto scenario too once we fixed one or a lot of databases from a SQL Server instance (source server) to a different server (destination server) wherever all logins mapped to those databases aren’t created on a destination server, or they’re created however with completely different SID. Anyway, if you came to matters you expected then it’s ok, but if not, it’s obligatory to fastidiously take into account the recommendation in this warning that claims we’d like to transfer possession of schemas to a different user.
But there are cases wherever these steps can fail. for example, if the login you wish to drop is ready as a information owner for one or a lot of databases. It that case, you’ll get following screens in SSMS:
If you used T-SQL, you’ll get following error message:
In this article, we’ll attempt to drop a SQL Server Login and everyone its dependencies victimization T-SQL statements. This check and its conclusions can offer United States of America relevant data that {we can we will we square measure able to} use if we have a tendency to are willing to alter this task or a minimum of some components of it.
In the following sections, we are going to 1st outline and build a test suit. Our objective is to drop a SQL Server Login [ApplicationSQLUser1] and its corresponding databases users, each known as [User]. We’ll 1st begin attempting to get rid of [User] information user from [Db1] information. Once we have a tendency to manage to perform this task, we have a tendency to will review the steps we followed and take into account their acceptable sequencing. Once this is often done, we’ll take into account the removal of the login itself and additionally enlist the actions that ought to be done.
Test case scenario:
Explanation
In this section, we are going to gift matters to that we are going to refer during this article wherever we wish to drop a SQL Server login known as [ApplicationSQLUser1], that’s mapped to 2 SQL Server databases, [Db1] and [Db2]. This mapping is finished between this login an information user known as [User] in [Db1] and [dbo] in [Db2]. Actually, information [Db2] is owned by [ApplicationSQLUser1] login. There’s additionally another login known as [ApplicationSQLUser2] that’s mapped to [UserB] in [Db1] information.
There square measure another necessary facts:
- At server level:
- [ApplicationSQLUser1] login is that the owner of [Db3] information
- [ApplicationSQLUser1] login is granted CONNECT SQL permission and might grant it to any SQL Server Login.
- [ApplicationSQLUser1] login granted CONNECT SQL permission to
- [ApplicationSQLUser2] login
- On a SQL Server 2012+ instances, [ApplicationSQLUser1] login is that the owner of a Server Role known as [ServerRoleOwnedByUser1].
- In [Db1] database:
- [UserA] is that the owner a schema known as [ApplicationSchema1]
- [UserA] is ready as fiduciary for a procedure called:
- [ApplicationSchema1].[sp_ExecuteAsUserA].
- [UserB] has been granted the EXECUTE permission for that procedure by
- [UserA].
Diagram that depicts matters:
Here may be a diagram that represents matters delineate in previous subdivision.
You will notice below necessary commands to make this example in your setting. This code is often found within the script known as “DropLogin-DropDbUser.tests.sql” connected to the current article.
Create LOGINs
*/
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ApplicationSQLUser1')
CREATE LOGIN [ApplicationSQLUser1] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ApplicationSQLUser2')
CREATE LOGIN [ApplicationSQLUser2] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
GRANT CONNECT SQL to ApplicationSQLUser1 WITH GRANT OPTION;
GO
grant CONNECT SQL to ApplicationSQLUser2 AS ApplicationSQLUser1;
GO
IF( (SELECT @@MICROSOFTVERSION / 0x01000000) >= 11 )
BEGIN
EXEC sp_executesql N'CREATE SERVER ROLE [ServerRoleOwnedByUser1] AUTHORIZATION [ApplicationSQLUser1];';
END;
GO
/*
Set MAPPINGS
create schemas
set schema ownership and default schemas
*/
USE [Db1]
CREATE USER [UserA] FOR LOGIN [ApplicationSQLUser1]
GO
CREATE USER [UserB] FOR LOGIN [ApplicationSQLUser2]
GO
CREATE ROLE [RoleOwnedByUserA] AUTHORIZATION [UserA];
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [UserA]
GO
CREATE SCHEMA ApplicationSchema1 AUTHORIZATION UserA;
GO
ALTER USER [UserA] WITH DEFAULT_SCHEMA=[ApplicationSchema1]
GO
ALTER USER [UserB] WITH DEFAULT_SCHEMA=[ApplicationSchema1]
GO
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::Db2 TO ApplicationSQLUser1;
/*
maps ApplicationSQLUser1 to [dbo] database user in Db2 database
*/
GO
/*
Create stored procedure referencing UserA in Db1
*/
Use Db1;
GO
CREATE PROCEDURE [ApplicationSchema1].[sp_ExecuteAsUserA]
WITH EXECUTE AS 'UserA'
AS
BEGIN
SELECT @@SERVERNAME
END;
GO
grant execute on OBJECT::[ApplicationSchema1].[sp_ExecuteAsUserA] to UserB AS UserA;
GO
Dropping an information user:
In this section, we’ll build completely different tests (based on test suit situation) so we will conclude on what we must always do to create a decent hold on procedure that drops information users.
Note:
These operations ought to be done solely when a backup (full, differential or log)
This backup ought to be taken with none consumer affiliation except yours
If a retardant happens, you’ll be able to restore this backup.
Manual drop of information user [UserA]
Let’s attempt to run following question and check what happens.
Use Db1 ;
GO
DROP USER UserA ;
GO
Here is our 1st error message that claims we have a tendency to cannot drop this user as a result of it’s the owner of 1 or a lot of information schemas.
This is not a retardant, let’s simply run the subsequent statement so this message can disappear. Note that it’s pretty easy here as a result of we’ve one schema.
USE Db1 ;GO
ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO [dbo] ;
GO
Note:
- Here we have a tendency to selected [dbo] information user however there’s no obligation to use that one.
- There is a modification in permission granter when this script has been dead. To be sure, simply run following statement to line back [UserA] as schema owner:
USE Db1
4 5 6 7 | USE Db1 ; ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO UserA ; GO grant execute on OBJECT::[ApplicationSchema1].[sp_ExecuteAsUserA] to UserB AS UserA; GO |
We have a permission set by [UserA] as we will see when running following query:
Use Db1;
select *
From sys.database_permissions
where grantor_principal_id = USER_ID(‘UserA’)
We’ll get following results set:
If we have a tendency to run once more the alter authorization on [ApplicationSchema1] to line it back to [dbo] information user:
USE Db1;
GO
ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO [dbo] ;
GO
And run once more previous question against sys. database permissions, we have a tendency to get associate degree empty result set:
Let’s check information permissions allotted to [UserB] information user with next query:
<br> Use Db1;<br> select * <br> From sys.database_permissions <br> where grantee_principal_id = USER_ID(‘UserB’)
We’ll get following results and that we can see that the permission has merely been revoked.
This means that we have a tendency to probably skin one or a lot of consumer applications and that we ought to store all permissions allotted by UserA before running this statement.
Let’s suppose everything went for sure and rehear to drop information user:
Use Db1 ;
GO
DROP USER UserA ;
GO
Again, it failing as a result of [UserA] owns [Role Owned By UserA] information role:
Let’s solve this drawback by sterilization authorization to information user on role [RoleOwnedByUserA]. this is often achieved by running following statement:
Use Db1 ;
GO
ALTER AUTHORIZATION ON
ROLE::RoleOwnedByUserA TO [dbo] ;
Now, let’s attempt over again to drop information user. Guess what is going to happen when running DROP USER statement…
Use Db1 ;
GO
DROP USER UserA ;
GO
Actually, it failed to succeed as a result of we’ve a hold on procedure that references this information user…
Here, we have a tendency to may have a retardant as a result of we have a tendency to may build associate degree application that works at the instant utterly fail. So, it’s suggested to check code modifications!
Here, we have a tendency to square measure positive of what we have a tendency to do, thus we’ll modify the hold on procedure so it’ll execute as [dbo] information user.
Use Db1;
GO
ALTER PROCEDURE [ApplicationSchema1].[sp_ExecuteAsUserA]
WITH EXECUTE AS ‘dbo’
AS
BEGIN
SELECT @@SERVERNAME
END;
GO
Let’s attempt once more to drop [UserA] information user. This time, it ought to succeed…
Use Db1 ;
GO
DROP USER UserA ;
GO
Indeed:
Let’s attempt an additional issue and make another time our [UserA] information user. Let’s set this user as a member of a integral information role, for example [db_ddladmin]. To do so, we will run following T-SQL script:
USE Db1 ;
CREATE USER [UserA] FOR LOGIN [ApplicationSQLUser1]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [UserA]
GO
Now, let’s attempt to drop [UserA] and check if it ends successfully…
Use Db1 ;
GO
DROP USER UserA ;
GO
And it worked!
Operations performed altogether
Here is that the complete script of the actions performed higher than.
USE Db1 ;
GO
grant execute on OBJECT::[ApplicationSchema1].[sp_ExecuteAsUserA] to UserB AS UserA;
GO
ALTER AUTHORIZATION ON SCHEMA::ApplicationSchema1 TO [dbo] ;
GO
ALTER AUTHORIZATION ON ROLE::RoleOwnedByUserA TO [dbo] ;
GO
ALTER PROCEDURE [ApplicationSchema1].[sp_ExecuteAsUserA]
WITH EXECUTE AS ‘dbo’
AS
BEGIN
SELECT @@SERVERNAME
END;
GO
DROP USER UserA ;
GO
Conclusions from tests
In this section, we have a tendency to did native trials to drop an information user. we have a tendency to had to create choices and changes to our information so we have a tendency to were ready to meet our goal. Now, it’s time to summarize what we have a tendency to have learned so we’d have everything outlined to put in writing a hold on procedure that will all the actions we found obligatory to truly drop an information user.
First, we’ve to store permissions our information user granted to different users in an exceedingly temporary table. We will do that with the subsequent question already shown higher than.
Use Db1;
select *
From sys.database_permissions
where grantor_principal_id = USER_ID(‘UserA’)
Why would we have a tendency to do that? as a result of associate degree ALTER AUTHORIZATION statement on a schema can take away all permissions granted on the objects of that schema. this is often expected behavior of that statement as we will see on Microsoft’s documentation page :
So, once we have a tendency to ran the ALTER AUTHORIZATION statement, we’ve to depute these permissions.
Then, we must always additionally take into account role ownerships and that we ought to depute them to a user. Again, this is often done by ALTER AUTHORIZATION {and we have a tendency to and that we} ought to pay attention of what we do here, however if we have a tendency to already saved information permissions, then we’ve everything we’d like to line the permissions on roles back to what they were. To demonstrate this, let’s run following statement that may grant a permission on [Role Owned By UserA] information role:
USE Db1 ;
GO
grant take ownership on ROLE::RoleOwnedByUserA to UserB
GO
Let’s check permissions allotted to [UserB]…
Query:
Use Db1;
select *
From sys.database_permissions
where grantee_principal_id = USER_ID(‘UserB’)
Results:
Finally, we have a tendency to had to review existing code so we have a tendency to can’t notice any regard to [UserA] within the body of procedural objects (stored procedures, functions, etc).
However, there’s an additional issue we will conclude. It’s that there’s associate degree order to respect if we wish to alter the removal of a information user U:
- Check
for references to information user
U within procedure
objects. If any is found, then raise a slip-up.
- Take acceptable action between following
ones:
- If a slip-up occurred in step one, review code then rehear step one
- If no
error occurred, head to step three.
- Now we will store information permissions allotted by information user U into a brief table.
- List
all information schemas owned by information user U and set a brand new owner for them.
- List
all information roles owned by information user U and set a brand new owner for them.
- Drop information user U.
Dropping a SQL Server Login:
Dropping all information users mapped to a login
In our check scenario, there have been 2 information mappings outlined for [ApplicationSQLUser1] login. We have a tendency to already manage to drop the foremost troublesome one, [UserA] in [Db1] information. As we’d like to get rid of any reference in any mapped information, it’s time to contemplate the other.
Actually, this mapping is finished to information user, that may be a reserved user that we have a tendency to can’t drop. It’s a consequence of the actual fact that [ApplicationSQLUser1] login owns. We’ll review this in an exceedingly moment and it won’t be a retardant any longer.
However, if we have a tendency to be before of another user than , we must always follow the precise same path as we have a tendency to do for [UserA] user in [Db1] information.
Manual removal of SQL Server Login [ApplicationSQLUser1]
Let’s attempt to drop this login directly by running following statement:
USE master;
GO
DROP LOGIN [ApplicationSQLUser1];
Unfortunately, this statement can fail with following error:
This means that we must always take a duplicate of permissions granted by this login and revoke them and grant them once more as another SQL Server login like [sa]. we will list existing server permissions victimization the subsequent query:
select *
from master.sys.server_permissions
where grantor_principal_id = SUSER_ID(‘ApplicationSQLUser1’);
In this test suit, we are going to get following results:
We can solve this by granting the CONNECT SQL permission as [sa] as follows:
USE master ;
GO
REVOKE CONNECT SQL FROM ApplicationSQLUser2 AS ApplicationSQLUser1;
GO
GRANT CONNECT SQL TO ApplicationSQLUser2 AS SA;
GO
If we have a tendency to run once more the question to induce back server level permissions, we’ll get associate degree empty result set:
Now, we have a tendency to square measure prepared for an additional trial to get rid of [ApplicationSQLUser1] login…
USE master;
GO
DROP LOGIN [ApplicationSQLUser1];
Here, we have a tendency to get another error message stating that we’d like to review server role ownerships:
Cleanups:
Now we have a tendency to finished our tests, we will cleanup what we’ve created. we will run following script to try and do that:
USE master;
GO
DROP DATABASE [Db1];
GO
DROP DATABASE [Db2];
GO
/*
Already dropped (this is the purpose of this article)
DROP LOGIN [ApplicationSQLUser1];
GO
*/
DROP LOGIN [ApplicationSQLUser2];
GO
IF( (SELECT @@MICROSOFTVERSION / 0x01000000) >= 11 )
BEGIN
EXEC sp_executesql N’DROP SERVER ROLE [ServerRoleOwnedByUser1];’;
END;
GO
Conclusion:
As we’ve seen on this text, it’s not that straightforward to properly drop a SQL Server login. There square measure several steps to follow and a few of those aren’t that simple: we’d ought to review procedural code and permissions so as to simply do what we wish to d
Nevertheless, we have a tendency to known the actions to require for login and information user removal and that we may conclude that these actions can be (partially) scripted or place in one or a lot of hold on procedure.
Like!! I blog frequently and I really thank you for your content. The article has truly peaked my interest.
My spouse and I stumbled over here coming from a different page and thought I might
check things out. I like what I see so now i’m following
you. Look forward to looking over your web page repeatedly.
When someone writes an article he/she keeps the thought of a user in his/her mind that
how a user can know it. Therefore that’s why this post is great.
Thanks!
Thanks for finally talking about > Droplet of a SQL Server Login and
all its dependences – Solution Views < Loved it!
Hello! I’m at work surfing around your blog from my new iphone 3gs!
Just wanted to say I love reading through your blog and look forward to all your posts!
Carry on the great work!
If some one desires to be updated with latest technologies after
that he must be visit this site and be up to date all the time.
Very good post. I’m experiencing a few of these issues as
well..