How to list the access the roles the different users have?

– print usernames and the roles assigned to the user, it can be run against any database and it will print the users and the roles assigned to different users
SELECT sl.name, g.name
FROM sysusers u, sysusers g, sysmembers m, master.dbo.syslogins sl
where g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
and g.uid = m.groupuid
and sl.sid = u.sid
and g.issqlrole = 1
and u.uid = m.memberuid
and sl.denylogin = 0
order by sl.name

Sample output:
localuser db_owner
localuser db_datareader
domain\ADuser db_datareader
domain\ADuser db_denydatawriter
….

Information on sys.databases

Using sysdatabases one can create script various scripts one of the useful is to create script to backup all databases.

select name, 'BACKUP DATABASE ' + name + ' to disk=' + '''c:\sqlbackup\' + name + '.bak''' from sysdatabases;

Here is documentation on sys.databases on different SQL Server versions.
sysdatabases (SQLServer 2008)
sysdatabases (SQLServer 2005)
sysdatabases (SQLServer 2005)

How to find sizes of all tables in SQLServer?

By executing the store procedure sp_spaceused ‘<table>’ one can find the space used by a table. The store procedure returns amount of space used by data, index and unused space. Here is the documentation on sp_spaceused from Microsoft.

Using the following link you can use the store proc below to return for all tables in a database http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx.

How to change logical name of SQL Server database?

To change the logical name of the SQL Server databse one can follow the steps below to change the logical name of database data file and log file. You may want to take a backup of the database before applying the change to ensure you have a rollback.

Example:
Use test
select fileid, name, filename from sysfiles
fileid name filename
1 test C:\MSSQL\Data\test.mdf
2 test_Log C:\MSSQL\Data\test_log.LDF

– rename data file
ALTER DATABASE Monitor MODIFY FILE (NAME = test, NEWNAME = App_Data)
GO
– rename log file
ALTER DATABASE Monitor MODIFY FILE (NAME = test_Log, NEWNAME = App_Log)
GO

– verify change
select fileid, name, filename from sysfiles
fileid name filename
1 App_Data C:\MSSQL\Data\test.mdf
2 App_Log C:\MSSQL\Data\test_log.LDF

How to get list of tables in SQLServer?

Using the SQL below one can list SQL Server tables

Use testDB
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

table_name
———–
ExceptionLog
Employee
…

How to extract start and end time of a job in SQL Server?

In Query analyzer

– prints job name, step name, start time and end time of each step in the job and sorts by latest on the top
– if jh.sql_message_id is 0 then that step failed but that status is set set at the overall job outcome
SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
and j.name = ‘Job_name’
ORDER BY run_date desc, run_time desc

Sample Output:

Job name	Step Name	Start Time	End Time
job_name	step_name	2009-05-13 12:55:01.000	2009-05-13 23:26:01.000
job_name	(Job outcome)	2009-05-13 12:55:01.000	2009-05-13 23:26:01.000
…	…	…	…

How to truncate time in date field?

In Oracle:

SQL> ALTER SESSION SET nls_date_format = ‘YYYY-MON-DD HH24:MI:SS’;

Session altered.

SQL> SELECT TRUNC(sysdate) FROM DUAL;

TRUNC(SYSDATE)
——————–
2009-MAY-10 00:00:00

In SQL Server:

SELECT CAST(CONVERT(varchar, GetDate(), 101) AS DateTime)
2009-05-10 00:00:00.000

How to shrink transaction log file for a database?

The following article gives some background on how to stop the transaction log from growing

Using the steps below one can shrink the transaction log.

Use TestDB
SELECT file_id, name FROM sys.database_files
GO

file_id name
1 TestDB
2 TestDB_log

DBCC SHRINKFILE (‘TestDB_log’, 2)
GO
BACKUP LOG TestDB WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (‘TestDB_log’, 2)
GO

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