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

Want to get the current Platform in which SQL is running? Easy to do

Easy to do:

create table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))

                        insert #SVer exec master.dbo.xp_msver

SELECT (select Value from #SVer where Name = N'Platform') AS [Platform]

                        drop table #SVer

Results:
Platform
NT INTEL X86

Server features and some server properties?

Easy to do:

SELECT

CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],

CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],

@@MAX_PRECISION AS [MaxPrecision],

SERVERPROPERTY(N'ProductVersion') AS [VersionString],

CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],

CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],

CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],

CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],

convert(sysname, serverproperty(N'collation')) AS [Collation],

CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],

CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],

SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],

SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],

SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],

SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],

SERVERPROPERTY(N'CollationID') AS [CollationID],

SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],

SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],

SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],

SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],

SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName]

Results:

IsFullTextInstalled	1
IsCaseSensitive	0
MaxPrecision	38
VersionString	10.0.4000.0
Edition	Standard Edition
ProductLevel	SP2
IsSingleUser	0
EngineEdition	2
Collation	SQL_Latin1_General_CP1_CI_AS
IsClustered	0
NetName	MYSERVER
BuildClrVersionString	v2.0.50727
ComputerNamePhysicalNetBIOS	MYSERVER
ResourceVersionString	10.00.4000
ResourceLastUpdateDateTime	11:59.2
CollationID	872468488
ComparisonStyle	196609
SqlCharSet	1
SqlCharSetName	iso_1
SqlSortOrder	52
SqlSortOrderName	nocase_iso

Which is the SQL Server Install Path?

Easy to do. This tutorial shows how to read registry keys also:

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
select @SmoRoot

Results:
c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL

Rename a database with a query? Easy

Easy:

ALTER DATABASE [perro] MODIFY NAME = [gato]

Results:

Change the database name from perro to gato.

Get extended properties of a table and columns? Easy.

Want to get the DESCRIPTION and other extended features of the table GATO1? easy:

select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'BaseType') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], 'MaxLength') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Precision') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], 'Scale') as int), xp.minor_id, COL_NAME(xp.major_id, xp.minor_id)  from sys.extended_properties xp  where xp.class = 1 and  xp.minor_id > 0 and xp.major_id = object_id(N'dbo.GATO1')  and xp.name in (N'MS_Description')  order by xp.minor_id

Results:

Adding extended properties to the table on SQL Server? (e.g. Description)

Easy to do:

use gato

DECLARE @v sql_variant

SET @v = N'DES'

EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'GATO1', N'COLUMN', N'ID'

Results:

Add description to the TABLE GATO1 on ID column. The description is DES

Change the password of the account? easy.

Easy to do (Changing the password of my account to Bolivia):

ALTER LOGIN [demo] WITH PASSWORD=N'bolivia'

Enable or disable an account using a query? Easy.

Easy to do:

/*Enable account*/

ALTER LOGIN [demo90] ENABLE

/*Disable account*/

ALTER LOGIN [demo90] DISABLE

Do you want to delete a DB with the backup history? Easy (SQL Server)

Easy to do:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'FIN63_JAP'
DROP DATABASE [FIN63_JAP]

Results:

Deletes the database including backup information.