How to create SQL scripts to recreate grants on a database granted through a role?
DECLARE @role_name VARCHAR(100)
DECLARE @member_name VARCHAR(100)
DECLARE @rolescur CURSOR
DECLARE @rolememberscur CURSOR
DECLARE @loginname VARCHAR(100)
DECLARE @username VARCHAR(100)
DECLARE @logincur CURSOR
PRINT '– Grant user access'
SET @logincur = CURSOR FOR SELECT l.name, u.name
FROM master..sysxlogins l, sysusers u
WHERE l.sid = u.sid AND l.name <> 'sa'
OPEN @logincur
FETCH NEXT FROM @logincur INTO @loginname, @username
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXEC sp_grantdbaccess ''' + @loginname + ''', ''' + @username + ''''
FETCH NEXT FROM @logincur INTO @loginname, @username
END
CLOSE @logincur
DEALLOCATE @logincur
— cursor for roles (user and built-in)
SET @rolescur = CURSOR FOR SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1) AND name <> 'db_owner'
— loop through roles
OPEN @rolescur
FETCH NEXT FROM @rolescur INTO @role_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '— ' + @role_name
— cursor for members in the roles
SET @rolememberscur = CURSOR FOR SELECT u.name
from sysusers u, sysusers g, sysmembers m
where g.name = @role_name
and g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid
OPEN @rolememberscur
FETCH NEXT FROM @rolememberscur INTO @member_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'exec sp_addrolemember ''' + @role_name + ''', ''' + @member_name + ''''
FETCH NEXT FROM @rolememberscur INTO @member_name
END
CLOSE @rolememberscur
DEALLOCATE @rolememberscur
— PRINT @role_name
FETCH NEXT FROM @rolescur INTO @role_name
END
CLOSE @rolescur
DEALLOCATE @rolescur
How to restore archive logs from backup piece?
If one needs to restore archive logs to disk and not apply them, for example in case when cloning a database manually and one is missing the archive logs from the source database and you need to restore the archive logs on the source machine. So using the following command one can restore the archive logs.
connect target /
connect rcvcat rmancatalog/rmancatalog@catdb
run {
restore archivelog from logseq=36747 until logseq=36753 thread=1;
}
from logseq – is the starting log sequence #
until logseq – is the end log sequence #
How to list files by their size?
# Using ls and sort one can sort the files by their size, here is what the options means
# ls -l – means print the file in long format, prints access, woner, group, size, last update and name
# sort -n -k5 – sort using numeric, -k5 field number to sort by
$ ls -l | sort -n -k5
