Finding and dropping Orphan Users in a DB

Below script can be used to find and drop the orphan users in a Database

 

 

Below script will list all the Orphan Users in a DB, we can select the output of [Script_To_Drop_User] column of the table variable and execute the same to Drop the user.

 

 -- Change the DB as per your DB Name

 

Use [PubDB]
declare @tbl_orpusers table (name varchar(260),[sid] varchar(100))

insert @tbl_orpusers
exec sp_change_users_login 'report'

select name [Orphan User],'DROP USER ['+name+']' [Script_To_Drop_User]from @tbl_orpusers



Checking the Orphan Users:

 

 

 

 

 

 

 

Below Link can be used to work on Orphan users in a DB,

 

http://msdn.microsoft.com/en-IN/library/ms175475.aspx

http://msdn.microsoft.com/en-IN/library/ms174378.aspx

 

 

 

Users and permissions in Database

Below Script is used to find the Users in Database and their permissions

SQL Server 2000

use [master]

select db_name(0)AS DBName, a.name, CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'

from  master..syslogins sl join dbo.sysusers a  on a.sid=sl.sid   LEFT OUTER JOIN dbo.sysmembers b ON a.uid=b.memberuid where a.altuid<>1 and a.uid not in (1,2) AND 'DBName' NOT IN ('master','msdb','model','tempdb') ORDER BY a.Name

SQL Server 2008

use [master]

Select db_name(0) As DBName,a.name 'User_Name',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

--where a.altuid<>1

SQL Server 2008

use master

Select db_name(0) As DBName,a.name 'User_Name',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID',

    default_schema_name

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

left join sys.database_principals dp on dp.principal_id=a.uid

--where a.altuid<>1

Script to find User Permissions on all DB's in a SQL Server 

create table #DBuserperm (DBName varchar(100),User_Name varchar(100),Login_Type varchar(100)

,Associated_Role varchar(100),LoginID varchar(100),Default_Schema_Name varchar(100),)

declare @dbname varchar(100)

declare @sqlQuery nvarchar(4000)

declare dbcursor CURSOR for

select name from sys.databases

where name not in ('master','model','msdb','tempdb')

OPEN dbcursor

FETCH NEXT FROM dbcursor

into @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sqlQuery='Use ['+@dbname+']'

      set @sqlQuery =@sqlQuery+ 'Select db_name(0) As DBName,a.name '''+'User_Name'+''',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN '''+'Windows Group'+'''

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN '''+'Windows Login'+'''

    WHEN a.issqlrole=1 THEN '''+'Database Role'+'''

    ELSE '''+'SQL Login'+''' END AS '''+'Login Type'+''',USER_NAME(b.groupuid) AS '''+'AssociatedRole'+''' ,

    sl.name '''+'Login_ID'+''',

    default_schema_name

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

left join sys.database_principals dp on dp.principal_id=a.uid'

insert into #DBuserperm

      exec sp_executesql @sqlQuery 

FETCH NEXT FROM dbcursor

into @dbname

END

select * from #DBuserperm

drop table #DBuserperm

CLOSE dbcursor

Deallocate dbcursor

OpenDataSource OpenQuery OpenRowset

--- OpenDataSource Example for connectiong another SQL Server and collecting data

---(Provides ad hoc connection information as part of a four-part object name without using a linked server name)

select * from

opendatasource('SQLNCLI','Data Source=muralixp;Integrated Security=SSPI;Connect Timeout=10').master.sys.databases

select * from

opendatasource('SQLNCLI','Data Source=murali-HP;User ID=sa;Password=xxxxxxx;Connect Timeout=10').master.sys.databases

--- OpenDataSource Example for connectiong Excel Sheet and collecting data. Shall mention linked server if we get error

--- Provides ad hoc connection information as part of a four-part object name without using a linked server name

select * from

opendatasource('Microsoft.Jet.OLEDB.4.0',

'Data Source=I:\SQL Server Own Material\Testing\Stars_Info.xls;Extended Properties=EXCEL 12.0')...[Info$] ;

--- OpenRowSet Example for connection another SQL Server and collecting data

select a.* from

openrowset('SQLNCLI','Server=murali-hp;trusted_connection=yes;',

'select * from master.sys.databases') as a

select *

from openrowset('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=I:\SQL Server Own Material\Testing\Stars_Info.xlsx;',

'select * from [info$]')

--- OpenQuery Example, connecting to linked server and collecting data

select * from openquery([MURALI-HP], 'SELECT employeeid FROM mssqltips.dbo.employee WHERE employeeid>1')

delete OPENQUERY ([MURALI-HP], 'SELECT employeeid FROM mssqltips.dbo.employee WHERE employeeid=6')

SQL Server Databases size usage details

Set nocount on

Declare @cmd varchar(1000)

set @cmd=' USE [?]; SELECT getdate() Report_Date , db_name() DBName,a.type_desc File_Type,

a.file_id as [FileId],

a.name as [LogicalName],

CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) as [FileSize(MB)],

CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'''

+'SpaceUsed'+''')/128.000,2)) as [SpaceUsed(MB)], CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'''

+'SpaceUsed'+'''))/128.000,2)) as [FreeSpace(MB)], round((CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'''

+'SpaceUsed'+''')/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) as [Pct_SpaceUsed],

round((CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'''

+'SpaceUsed'+'''))/128.000,2))/ CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)))*100,2) as [Pct_FreeSpace]

FROM sys.database_files a LEFT OUTER JOIN sys.data_spaces b

ON a.data_space_id = b.data_space_id'

Create table #DBFilesDetails(Report_Date datetime,DBName varchar(200),File_Type varchar(20),FileID int,

LogicalName varchar(400),[FileSize(MB)] decimal(20,2),[SpaceUsed(MB)] decimal(20,2), [FreeSpace(MB)]

decimal(20,2),[Pct_Spaceused] decimal(20,2),[Pct_FreeSpace] decimal(20,2))

insert #DBFilesDetails EXEC sp_MSforeachdb @cmd

select * from #DBFilesDetails

Drop Table #DBFilesDetails