Author: sqldba

Problem: Unable to run a query through a linked server SQL Server 2008 R2. This problem occurs with a non-sysadmin account.

Error Message:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter “User Name”   into the @provstr

Note: Also do not use ‘SQL Server’ (note space) as product name, as you cannot specify a provider or any properties for product ‘SQL Server’

Resolution : Add “User ID=remoteLogin” into the provider string on your linked server

EXEC master.dbo.sp_addlinkedserver @server = N’LinkServerName’, @provider=N’SQLNCLI’,@srvproduct = ‘SQLSERVER’, @provstr=N’SERVER=serverNameInstanceName;User ID=remoteLogin’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’LinkServerName’, @locallogin = ‘localLogin’ , @useself = N’False’, @rmtuser = N’remoteLogin’, @rmtpassword = N’*****’

Check:

SELECT * FROM OPENQUERY ([LinkServerName], ‘SELECT * FROM sysobjects’)

   SELECT name

        , object_definition(object_id)

     FROM sys.procedures

    WHERE object_definition(object_id) LIKE ‘%SomeSearchTerm%’

-- =============================================
--      Author: John Olsen
-- Create date: 20120323
-- Update date: 20120425
-- Review date: 
-- Description: Generate column related t-sql
--              and c# code for specified table
--              because i have had carpul
--              tunnel surgery once already
-- =============================================
ALTER PROCEDURE [dbo].[uCodeGenerator] ( @tabname varchar(128) )
AS
BEGIN

-------------------------------------
--DECLARE @tabname varchar(128)
--SET @tabname = 'truck_transfers'
-------------------------------------

SET NOCOUNT ON;

DECLARE @types TABLE ( xtype tinyint
                     , sql_type varchar(128)
                     , csharp_type varchar(30)
                     , db_type varchar(128)
                     , use_len tinyint
                     , use_prec tinyint
                     , use_scale tinyint
                     , type_class tinyint )

       INSERT INTO @types (xtype, sql_type, csharp_type, db_type, use_len, use_prec, use_scale, type_class)
            SELECT 127, 'bigint', 'int', 'BigInt',0,0,0,1
      UNION SELECT 173, 'binary', 'byte[]', 'Binary',0,0,0,1
      UNION SELECT 104, 'bit', 'int', 'Bit',0,0,0,1
      UNION SELECT 175, 'char', 'string', 'Char',1,1,0,2
      UNION SELECT 40, 'date', 'string', 'VarChar',1,1,0,3
      UNION SELECT 61, 'datetime', 'string', 'DateTime',0,0,0,3
      UNION SELECT 42, 'datetime2', 'string', 'DateTime',0,0,0,3
      UNION SELECT 43, 'datetimeoffset', 'string', 'VarChar',1,1,0,3
      UNION SELECT 106, 'decimal', 'float', 'Decimal',0,1,1,1
      UNION SELECT 62, 'float', 'float', 'Float',0,1,1,1
      UNION SELECT 240, 'geography', 'SqlGeography', 'Geography',0,0,0,4
      UNION SELECT 240, 'geometry', 'SqlGeometry', 'Geometry',0,0,0,4
      UNION SELECT 240, 'hierarchyid', 'string', 'NVarChar',1,1,0,4
      UNION SELECT 34, 'image', 'byte[]', 'Image',0,0,0,4
      UNION SELECT 56, 'int', 'int', 'Int',0,0,0,1
      UNION SELECT 60, 'money', 'float', 'Money',0,0,0,1
      UNION SELECT 239, 'nchar', 'string', 'NChar',1,1,0,2
      UNION SELECT 99, 'ntext', 'string', 'NText',0,0,0,2
      UNION SELECT 108, 'numeric', 'float', 'Decimal',0,1,1,1
      UNION SELECT 231, 'nvarchar', 'string', 'NVarChar',1,1,0,2
      UNION SELECT 59, 'real', 'float', 'Real',0,1,1,1
      UNION SELECT 58, 'smalldatetime', 'string', 'SmallDateTime',0,0,0,3
      UNION SELECT 52, 'smallint', 'int', 'SmallInt',0,0,0,1
      UNION SELECT 122, 'smallmoney', 'float', 'SmallMoney',0,0,0,1
      UNION SELECT 98, 'sql_variant', 'object', 'Variant',0,0,0,4
      UNION SELECT 231, 'sysname', 'string', 'VarChar',1,1,0,2
      UNION SELECT 35, 'text', 'string', 'Text',0,0,0,2
      UNION SELECT 41, 'time', 'string', 'VarChar',0,0,0,3
      UNION SELECT 189, 'timestamp', 'string', 'Timestamp',0,0,0,3
      UNION SELECT 48, 'tinyint', 'int', 'TinyInt',0,0,0,1
      UNION SELECT 36, 'uniqueidentifier', 'SqlGuid', 'UniqueIdentifier',0,0,0,4
      UNION SELECT 165, 'varbinary', 'byte[]', 'VarBinary',1,1,0,4
      UNION SELECT 167, 'varchar', 'string', 'VarChar',1,1,0,2
      UNION SELECT 241, 'xml', 'string', 'Text',0,0,0,2

--SELECT * FROM @types

DECLARE @cols TABLE ( id int identity(1,1)
                    , colname varchar(128)
                    , parmname varchar(128)
                    , sql_type varchar(128)
                    , db_type varchar(128)
                    , csharp_type varchar(30)
                    )
DECLARE @i int
      , @crlf char(2)
      , @classname varchar(128)
      , @colname varchar(128)
      , @parmname varchar(128)
      , @sql_type varchar(128)
      , @db_type varchar(128)
      , @csharp_type varchar(30)
      , @s_dec varchar(8000)
      , @s_assn varchar(8000)
      , @s_collist varchar(8000)
      , @s_parmlist varchar(8000)
      , @c_dec varchar(8000)
      , @c_update varchar(8000)
      , @c_pop varchar(8000)
      , @c_parm varchar(8000)
      
SET @crlf = char(13) + char(10)

     INSERT INTO @cols ( colname, parmname, sql_type, db_type, csharp_type )
          SELECT B.name "colname"
               , '@' + B.name "parmname"
               , (case when C.use_scale = 1 then C.sql_type + '(' + cast(B.prec as varchar(30)) + ',' + cast(B.scale as varchar(30)) + ')'
                       when C.use_prec = 1 then C.sql_type + '(' + cast(B.prec as varchar(30)) + ')'
                       else C.sql_type end) "sql_type"
               , (case when C.use_len = 1 then 'SqlDbType.' + C.db_type + ', ' + cast(B.prec as varchar(30))
                       else 'SqlDbType.' + C.db_type end) "db_type"
               , C.csharp_type
            FROM sysobjects A (NOLOCK)
      INNER JOIN syscolumns B (NOLOCK)
              ON A.id = B.id
      INNER JOIN @types C
              ON B.xtype = C.xtype
           WHERE A.type = 'U'
             AND A.name = @tabname
        ORDER BY A.name ASC
               , B.colorder DESC

          SELECT @i = max(id) 
            FROM @cols

   SET @classname = dbo.fnTitleCase(@tabname)
   SET @s_parmlist = ''
   SET @s_collist = ''
   SET @s_dec = ''
   SET @s_assn = ''
   SET @c_dec = '   public class ' + @classname + 'DAO : DAO' + @crlf + '   {'
   SET @c_pop = '      public void populate(DataRow row)' + @crlf + '      {'
   SET @c_update = '      public void update()' + @crlf + '      {'
   SET @c_parm = ''

   WHILE @i > 0
    BEGIN
       SELECT @colname = A.colname
            , @parmname = A.parmname
            , @sql_type = A.sql_type
            , @db_type = A.db_type
            , @csharp_type = A.csharp_type
         FROM @cols A
        WHERE A.id = @i

        SET @s_collist += @crlf + '   , ' + @colname
        SET @s_parmlist += @crlf + '   , ' + @parmname
        SET @s_dec += @crlf + '   , ' + @parmname + ' ' + @sql_type
        SET @s_assn += @crlf + '   , A.' + @colname + ' = ' + @parmname
        SET @c_dec += @crlf + '      public ' + @csharp_type + ' ' + @colname + (case when @csharp_type = 'string' then ' = "";' else ' = 0;' end)
        SET @c_pop += @crlf + '         this.' + @colname + ' = ' 
                   + (case when @csharp_type in ('float','int') then @csharp_type + '.Parse(row["' + @colname + '"].ToString());'
                           else 'row["' + @colname + '"].ToString();' end )
        SET @c_update += @crlf + '         oCmd.Parameters.Add("' + @parmname + '", ' + @db_type + ').Value = this.' + @colname + ';'

       SET @i -= 1
    END

   PRINT @s_collist + @crlf
   PRINT @s_parmlist + @crlf
   PRINT @s_dec + @crlf
   PRINT @s_assn + @crlf + @crlf
   PRINT @c_dec + @crlf + @crlf
   PRINT @c_pop + @crlf +  '      }//populate()' + @crlf + @crlf
   PRINT @c_update + @crlf +  '      }//update()' + @crlf + @crlf
   PRINT @c_parm + @crlf
   PRINT '   } //' + @classname

END --uCodeGenerator()

You can return the results of a dynamic sql statement into a temp table or table variable:

DECLARE @tab TABLE ( id int, val varchar(100) )
DECLARE @sql varchar(8000)

SET @sql = "SELECT id, name FROM dbo.mytable"

INSERT INTO @tab ( id, val )
EXEC( @sql )

You have to open your browser (on server console or during a remote session) using “Run as Administrator”.

Then go to http://localhost/Reports (NOTE: do not put http:///Reports – it doesn’t work).

Go to Folder Settings and assign role browser and content manager to your administrator account. It should start working now.

If you are running it on Windows server 2008 don’t forget to create inbound rule in windows firewall to port 80.

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

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

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.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:temptryme.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)

$adOpenStatic = 3

$adLockOptimistic = 3

$adStatusOpen = 1

$sql = "Select * from dispatch;"

$cstr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:tempmydata.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()

}