SQLTeam.com | Weblogs | Forums

How to restrict users to a database


#1

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.

  1. 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.
  2. 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.
  3. Can renaming the database restrict all connections from application??

Please advice any options to restrict all user connections to a database.
3


#2

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.


#3

Thanks Stephen.

Can you please share the script to script out all users and permissions.


#4

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.


#5

What is your aim here? What is the purpose of you doing this? maintenance? company shutting down?


#6

Revoke connect on < db_name >
to < login >
will prevent a login from connecting


#7

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.


#8

Thnx for the query.

We have around 200 to 300 user logins on that database. So it is tough to script out all logins.
So i am just looking for a simple work around.


#9
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 ;

#10

Hi

There is a very easy way
SMO Objects SQL Server
PowerShell

Hope this helps

Please try this
I am very interested in your feedback

Need help with powershell
Lets Chat Online
I will try to help you line by line

PowerShell Code

function ConvertTo-SQLHashString{

param([parameter(Mandatory=$true)] $binhash)

$outstring = '0x'

$binhash | ForEach-Object {outstring += ('{0:X}' -f _).PadLeft(2, '0')}
return $outstring

}

Server Name

$source = 'DESKTOP-6F8T6UH'

FileName Where Logins Will Be

$filename = 'C:\Users\1 loc\Documents\SQL Server Management Studio\SQL Files\logins.sql'
$outfile = 'C:\Users\1 loc\Documents\SQL Server Management Studio\SQL Files\logins.sql'

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$smosource = new-object ('Microsoft.SqlServer.Management.Smo.Server') $source

$so = new-object microsoft.sqlserver.management.smo.scriptingoptions

$so.LoginSid = $true

if(!($logins))
{
$logins = (smosource.Logins | Where-Object {_.IsSystemObject -eq $false}).Name.Trim()
}

foreach ($loginname in $logins){

$login = $smosource.Logins[$loginname]
#Script out the login, remove the "DISABLE" statement included by the .Script() method

$lscript = $login.Script($so) | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}

$lscript = $lscript -join ' '


if($login.LoginType -eq 'SqlLogin'){
  $sql = "SELECT convert(varbinary(256),password_hash) as hashedpass FROM sys.sql_logins where name='"+$loginname+"'"
  $hashedpass = ($smosource.databases['tempdb'].ExecuteWithResults($sql)).Tables.hashedpass

  $passtring = ConvertTo-SQLHashString $hashedpass

  $rndpw = $lscript.Substring($lscript.IndexOf('PASSWORD'),$lscript.IndexOf(', SID')-$lscript.IndexOf('PASSWORD'))

  $comment = $lscript.Substring($lscript.IndexOf('/*'),$lscript.IndexOf('*/')-$lscript.IndexOf('/*')+2)

  $lscript = $lscript.Replace($comment,'')

  $lscript = $lscript.Replace($rndpw,"PASSWORD = $passtring HASHED")

}

#script login to out file
$lscript | Out-File -Append -FilePath $outfile
     
 }

#11

Hi

There is another way
From SQL Server Management Studio
You can Script Out Logins

Google .. its very easy
If you have trouble PING ME