Drop roles to the user “reader” with a query?

Just execute the query:

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'bulkadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'dbcreator'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'diskadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'processadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'securityadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'serveradmin'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'setupadmin'

EXEC master..sp_dropsrvrolemember @loginame = N'reader', @rolename = N'sysadmin'

The set of command will drop to the user reader different roles.

Assign roles to the user “reader” with a query?

Just execute the query:

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'bulkadmin'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'dbcreator'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'diskadmin'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'processadmin'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'securityadmin'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'serveradmin'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'setupadmin'

EXEC master..sp_addsrvrolemember @loginame = N'reader', @rolename = N'sysadmin'

The set of command will assign to the user reader different roles.

Create a Windows User from SQLServer? Easy

Just execute the query:

CREATE LOGIN [reader] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

Changing the Login Mode Between SQL Auth and Windows? Easy to query

Easy to do:

Change to Windows Authentication

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1

Change to SQL Authentication
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

Which are the top ten most expensive queries? SQL Server.

Easy to do:

select top 10 rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ) as row_no

,       (rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ))%2 as l1

,       creation_time

,       last_execution_time

,       (total_worker_time+0.0)/1000 as total_worker_time

,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]

,       total_logical_reads as [LogicalReads]

,       total_logical_writes as [LogicalWrites]

,       execution_count

,       total_logical_reads+total_logical_writes as [AggIO]

,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]

,       case when sql_handle IS NULL

                then ' '

                else ( substring(st.text,(qs.statement_start_offset+2)/2,       (case when qs.statement_end_offset = -1         then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )

        end as query_text

,       db_name(st.dbid) as db_name

,       st.objectid as object_id

from sys.dm_exec_query_stats  qs

cross apply sys.dm_exec_sql_text(sql_handle) st

where total_worker_time  > 0

order by [AvgCPUTime] desc

Results:

Log information from a single query? Easy.

Easy to do:

SELECT

CAST(CASE when s.growth=0 THEN (CASE WHEN s.type = 2 THEN 0 ELSE 99 END) ELSE s.is_percent_growth END AS int) AS [GrowthType],

s.name AS [Name],

s.file_id AS [ID],

s.physical_name AS [FileName],

s.size * CONVERT(float,8) AS [Size],

CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],

CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],

s.is_media_read_only AS [IsReadOnlyMedia],

s.is_read_only AS [IsReadOnly],

CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],

s.is_sparse AS [IsSparse]

FROM

sys.master_files AS s

WHERE

(s.name='defa_log')

Results:

GrowthType	Name	ID	FileName	Size	MaxSize	Growth	IsReadOnlyMedia	IsReadOnly	IsOffline	IsSparse
1	defa_log	2	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\defa_log.ldf	1024	2.147E+09	10	0	0	0	0

A File Exists from SQL Server Query?

Easy to do:

exec xp_fileexist 'c:\renepally.txt'

Results:

File Exists	1
File is a Directory	0
Parent Directory Exists	1