Mixing
Stored Procedure Execute as Owner Close Database Connections Not Working
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
I created a stored procedure to allow users to Close All Db connections in the QA environment. I am SA and created the procedure.
When I modify the SP , run it Without 'Execute as Owner', I get results.
When I add 'Execute as Owner', I do not receive any results.
Trying to understand why.
create procedure [dbo].[DatabaseConnectionClose]
@DatabaseName varchar(255)
with execute as owner
as
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id(@DatabaseName)
select @kill as CloseConnectionScript
EXEC(@kill);
sql-server sql-server-2012 permissions connections impersonation
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
asked Aug 22 at 17:37
MarkAllison
313
add a comment |Â
up vote
2
down vote
favorite
I created a stored procedure to allow users to Close All Db connections in the QA environment. I am SA and created the procedure.
When I modify the SP , run it Without 'Execute as Owner', I get results.
When I add 'Execute as Owner', I do not receive any results.
Trying to understand why.
create procedure [dbo].[DatabaseConnectionClose]
@DatabaseName varchar(255)
with execute as owner
as
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id(@DatabaseName)
select @kill as CloseConnectionScript
EXEC(@kill);
sql-server sql-server-2012 permissions connections impersonation
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
asked Aug 22 at 17:37
MarkAllison
313
2
The script you have is inferior because (a) there's a more efficient way to achieve the same thing and (b)database_idis unreliable since cross-database queries and locks can exist.SET @kill = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
– Aaron Bertrand♦
Aug 22 at 18:42
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I created a stored procedure to allow users to Close All Db connections in the QA environment. I am SA and created the procedure.
When I modify the SP , run it Without 'Execute as Owner', I get results.
When I add 'Execute as Owner', I do not receive any results.
Trying to understand why.
create procedure [dbo].[DatabaseConnectionClose]
@DatabaseName varchar(255)
with execute as owner
as
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id(@DatabaseName)
select @kill as CloseConnectionScript
EXEC(@kill);
sql-server sql-server-2012 permissions connections impersonation
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
asked Aug 22 at 17:37
MarkAllison
313
I created a stored procedure to allow users to Close All Db connections in the QA environment. I am SA and created the procedure.
When I modify the SP , run it Without 'Execute as Owner', I get results.
When I add 'Execute as Owner', I do not receive any results.
Trying to understand why.
create procedure [dbo].[DatabaseConnectionClose]
@DatabaseName varchar(255)
with execute as owner
as
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id(@DatabaseName)
select @kill as CloseConnectionScript
EXEC(@kill);
sql-server sql-server-2012 permissions connections impersonation
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
asked Aug 22 at 17:37
MarkAllison
313
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
edited Aug 22 at 19:12
Solomon Rutzky
45.3k473152
45.3k473152
asked Aug 22 at 17:37
MarkAllison
313
asked Aug 22 at 17:37
MarkAllison
313
asked Aug 22 at 17:37
MarkAllison
313
313
2
The script you have is inferior because (a) there's a more efficient way to achieve the same thing and (b)database_idis unreliable since cross-database queries and locks can exist.SET @kill = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
– Aaron Bertrand♦
Aug 22 at 18:42
add a comment |Â
2
The script you have is inferior because (a) there's a more efficient way to achieve the same thing and (b)database_idis unreliable since cross-database queries and locks can exist.SET @kill = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
– Aaron Bertrand♦
Aug 22 at 18:42
2
2
The script you have is inferior because (a) there's a more efficient way to achieve the same thing and (b)
database_id is unreliable since cross-database queries and locks can exist. SET @kill = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'– Aaron Bertrand♦
Aug 22 at 18:42
The script you have is inferior because (a) there's a more efficient way to achieve the same thing and (b)
database_id is unreliable since cross-database queries and locks can exist. SET @kill = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'– Aaron Bertrand♦
Aug 22 at 18:42
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
8
down vote
Using the EXECUTE AS clause of the CREATE module_type statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.
To do this properly:
- Get rid of
EXECUTE AS Implement this using Module Signing:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)
The only permission you should need to grant to the Certificate-based Login is:
ALTER ANY CONNECTION(as per the documentation for KILL)
For more info on module signing, please visit: Module Signing Info
Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).
However, if ALTER DATABASE [db_name] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
8
down vote
Using the EXECUTE AS clause of the CREATE module_type statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.
To do this properly:
- Get rid of
EXECUTE AS Implement this using Module Signing:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)
The only permission you should need to grant to the Certificate-based Login is:
ALTER ANY CONNECTION(as per the documentation for KILL)
For more info on module signing, please visit: Module Signing Info
Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).
However, if ALTER DATABASE [db_name] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
add a comment |Â
up vote
8
down vote
Using the EXECUTE AS clause of the CREATE module_type statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.
To do this properly:
- Get rid of
EXECUTE AS Implement this using Module Signing:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)
The only permission you should need to grant to the Certificate-based Login is:
ALTER ANY CONNECTION(as per the documentation for KILL)
For more info on module signing, please visit: Module Signing Info
Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).
However, if ALTER DATABASE [db_name] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
add a comment |Â
up vote
8
down vote
up vote
8
down vote
Using the EXECUTE AS clause of the CREATE module_type statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.
To do this properly:
- Get rid of
EXECUTE AS Implement this using Module Signing:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)
The only permission you should need to grant to the Certificate-based Login is:
ALTER ANY CONNECTION(as per the documentation for KILL)
For more info on module signing, please visit: Module Signing Info
Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).
However, if ALTER DATABASE [db_name] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
Using the EXECUTE AS clause of the CREATE module_type statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.
To do this properly:
- Get rid of
EXECUTE AS Implement this using Module Signing:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)
The only permission you should need to grant to the Certificate-based Login is:
ALTER ANY CONNECTION(as per the documentation for KILL)
For more info on module signing, please visit: Module Signing Info
Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).
However, if ALTER DATABASE [db_name] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
edited Aug 23 at 16:52
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
answered Aug 22 at 17:53
Solomon Rutzky
45.3k473152
45.3k473152
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215623%2fstored-procedure-execute-as-owner-close-database-connections-not-working%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
2
The script you have is inferior because (a) there's a more efficient way to achieve the same thing and (b)
database_idis unreliable since cross-database queries and locks can exist.SET @kill = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'– Aaron Bertrand♦
Aug 22 at 18:42