Friday 7 December 2012

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