Getting User Access of all Users in a Database

Hi,
Had a requirement to refresh the database in dev from prod but the criteria was to retain the user access for that db intact. So needed to work on this as a priority. I have broken down the task in a two phases.

1st phase:
To identify the script to generate the present user access for the users in a database. Below is mentioned the script which will help me get the list.

select su.name as UserName ,sg.name as AccessGroup from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid

2nd phase:
To identify the script which will help me restore the access for the users in dev enviroment after the database refresh.

select 'Exec sp_addrolemember ' +''''+ sg.name+ ''''+','+''''+ su.name+''''
from sysmembers sm
join sysusers su on sm.memberuid=su.uid
join sysusers sg on sg.uid=sm.groupuid


Tushar

Why no T-Log Backup possible in SIMPLE

When in SIMPLE recovery model a database log records in the active VLFs. These records are retained, as they may be required for a rollback operation. However, the inactive VLFs are truncated when a checkpoint occurs, meaning that the log records in these VLFs can be immediately overwritten with new log records. This is why a database operating in SIMPLE recovery is referred to as being in auto-truncate mode. In this mode, no "history" is maintained in the log and so it cannot be captured in a log backup and used as part of the restore process.

reference: sqlservercentral

Tushar

sp_lock and sp_who

Hi prepared a script to imitiate the sp_lock using DMV.

SELECT
tl.request_session_id as spid,
tl.resource_database_id as dbid,
tl.resource_associated_entity_id as objid,
tl.resource_type type,
tl.resource_description as resource,
tl.request_mode as mode,
tl.request_status as status
FROM sys.dm_tran_locks tl

Preparing a script for sp_who and sp_who2. Should be here soon.

Tushar