Hi,
Application team want to check if a database is still in use or not. So i have verified sessions there are no open transactions. So they want it to verify by restricting all user logins to that database.
We shouldnt take the database offline as it generate an alert, as DB level monitoring is not set we dont want alerts to be generated.
We cant take it into single user mode as there are lot of sessions connect to the server and out session will get logged out very soon.
Can renaming the database restrict all connections from application??
Please advice any options to restrict all user connections to a database.
3
Renaming the database may or may not restrict access but I certainly wouldn't do that.
I personally would script all the database users and permissions/role membership, save the scripts as a roll back and then drop the users. That way you know you are stopping users getting in.
Oh sorry, I haven't written one myself and if I were to post one on here it would just be plagiarising someone else's work.
If you search for "t-sql script database users and permissions" or something similar there is a stack of suggestions, you should be able to find one that suits your needs closely.
Essentially you will be starting with something like:
USE [database name];
GO
SELECT u1.name,
u1.type, u2.name
FROM sys.database_principals AS u1
INNER JOIN sys.database_role_members AS r
ON u1.principal_id = r.member_principal_id
INNER JOIN sys.database_principals AS u2
ON r.role_principal_id = u2.principal_id
WHERE u1.type <> 'R';
This will link the users table with the list of roles assigned to each user by joining the linking table.
You would need to then get a list of any explicitly defined permissions for each user but I hope the query above gives you a starting point.
We have a production server on which there are different applications hosted. Few DB's are migrated to new server. But before migration application team is not sure what databases are connected to the application. So to make sure of it they want to stop all connections to a database to check if the application is facing issue.
Now we have to stop all connections to that database.
DECLARE @sql nvarchar(1000) ;
DECLARE c CURSOR FOR
SELECT
'REVOKE CONNECT ON ' + Db_Name(Db_Id()) + ' TO ' + sp.name
FROM
sys.server_principals sp
JOIN
sys.database_principals dp ON sp.sid = dp.sid
WHERE
dp.sid <> SUser_SID()
AND sp.name NOT LIKE '##%##' ;
OPEN c ;
FETCH c
INTO
@sql ;
WHILE @@fetch_Status = 0
BEGIN
PRINT ( @sql ) ;
--EXEC(@sql) ;
FETCH c
INTO
@sql ;
END ;
CLOSE c ;
DEALLOCATE c ;