SQL Server Limiting Data Access for a Server Admin

If a user is granted the following server roles they still cannot access the data of databases they are not a user or dbo.


serveradmin - can change server configuration parameters and shut down the server.

setupadmin - can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption.

securityadmin - can create and manage server logins and auditing, and read the error logs.

processadmin - can manage the processes running in SQL Server.

dbcreator - can create, alter, and resize databases.

diskadmin - can manage disk files.



----------- example script -----------

use master
go



EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'securityadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'serveradmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'setupadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'processadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'diskadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'dbcreator'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'bulkadmin'
GO

----------- example script -----------
To allow backing up of a database they do have access to, then can use the following:


use [user_database]
go

grant backup database to [testuser]
go

Plan Cache Stats

Special thanks to Kimberly L. Tripp for this query

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go 

Full article: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx#ixzz00P7SU8xp

SQL Server Browser Service

Quick Tip from a respected MS Instructor: Disable SQL Server Browser Service

This causes a LOT of wasted network traffic and opens up a vulnerability making it easier for hackers to find your data servers.

Turn this off and advise your users to explicity enter the server name and not use the "browse" feature.

SQLActiveScriptHost Example

SQLActiveScriptHost.Print "Hello World"

Dim conn, rs
Dim sql, sProvider, sCString

''sProvider = "Microsoft.JET.OLEDB.4.0"
sProvider = "Microsoft.ACE.OLEDB.12.0"
sCString = "Data Source= c:\temp\tryme.mdb"

sql = "SELECT * FROM tryme;"

Set conn = SQLActiveScriptHost.CreateObject("ADODB.Connection")
With conn
.Provider = sProvider
.Mode = adModeRead
.ConnectionString = sCString
.Open
End With

SQLActiveScriptHost.Print "Connected..."

Set rs = conn.Execute(sql)

While Not rs.EOF
rs.MoveNext
WEnd

SQLActiveScriptHost.Print "Executed SQL..."

conn.Close
Set conn = Nothing

SQLActiveScriptHost.Print "Connection Closed."


Note: SQLActiveScriptHost is being deprecated in a future version of SQL Server (Post 2008 R2)

PowerShell Connect to MSAccess

$adOpenStatic = 3
$adLockOptimistic = 3
$adStatusOpen = 1
$sql = "Select * from dispatch;"
$cstr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:\temp\mydata.mdb; Jet OLEDB:Database Password=mypass;"

$conn = New-Object -comobject ADODB.Connection
$rs = New-Object -comobject ADODB.Recordset

$conn.Open($cstr)

if ($conn.State -eq $adStatusOpen)
{
   $rs.Open($sql, $conn, $adOpenStatic, $adLockOptimistic)

   if ($rs.State -eq $adStatusOpen)
   {
      $rs.MoveFirst()

      while (!$rs.EOF)
      {
         $rs.Fields.Item(1).Value;
         $rs.MoveNext()
      }
      $rs.Close()
   }

   $conn.Close()
}

varchar(MAX)

30 January 2012
by Rob Garrison
www.simple-talk.com

* Where appropriate, use VARCHAR(n) over VARCHAR(MAX) for reasons of good design if not performance benefits, and because VARCHAR(MAX) data does not compress

* Storing large strings takes longer than storing small strings.

* Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.

* Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.

* Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.


Full article
http://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar%28n%29-anymore/

restored database owner

Ran into an interesting problem with a SQL2005 database being restored to a SQL2008R2 instance.

Turned out the database owner was correctly represented as the login that restored the database, however the login mapped to the "dbo" user was still mapped to the old login on the source server.

To fix this, ran the following:

alter authorization on database::[database_name] to [login_name]
go