Connect to SQL Server When System Administrators Are Locked Out

http://msdn.microsoft.com/en-us/library/dd207004(v=sql.105).aspx

How to: Change Server Authentication Mode

http://msdn.microsoft.com/en-us/library/ms188670(v=SQL.105).aspx

these articles came in handy today…

declare @tabs TABLE ( id int identity(1,1), tabname sysname)
declare @res TABLE ( tabname sysname, numrows int )

declare @id int, @numrows int, @tabname sysname, @sql nvarchar(4000)

insert into @tabs (tabname)
select name from sysobjects where type = ‘U’ order by 1 asc

select @id = max(id) from @tabs

while @id > 0
 begin
   select @tabname = tabname from @tabs where id = @id
   set @sql = ‘select ”’ + @tabname + ”’ “tabname”, count(1) “numrows” from [‘ + @tabname + ‘]’

   insert into @res ( tabname, numrows )
   exec sp_executesql @sql

   set @id = @id – 1
 end

 select tabname, numrows from @res where numrows > 0 order by tabname

When you restore a 2005+ database from one instance to another, the database users are preserved in the database, but now they are linked to the GUID of the login on the originating instance.   The login may exist by the same name on the target server, but since the principal GUIDs are different the user is not linked to that login.  One possibility is to drop and recreate the user and/or login, but this is very destructive in the case that there are complex permissions involved for the user in the DB.

   DECLARE @tab TABLE ( id int identity(1,1), uname sysname )

   DECLARE @id int
         , @uname sysname
         , @sql nvarchar(4000)

     INSERT INTO @tab ( uname )
          SELECT name
            FROM sysusers 
           WHERE issqluser = 1 
             AND hasdbaccess = 1 
             AND name != 'dbo'

          SELECT @id = max(id) from @tab

   WHILE @id > 0
    BEGIN
          SELECT @uname = uname 
            FROM @tab 
           WHERE id = @id

      EXEC sp_change_users_login @action = 'Update_One', @UserNamePattern = @uname, @LoginName = @uname

      SET @id = @id - 1
    END

 Used to use sp_change_users_login, but since that is now deprecated, it is recommended to use the following:

Error: The maximum string content length quota (8192) has been exceeded while reading XML data.

Solution: Adjust buffer, message, and string content length in client’s app config or web config.

...

 
  
    
       
         
         
       
    
  
 

Example of a minimal implementation that includes Soap username and password.

First create the service:

-------------------------- websvc.cs ----------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Services;
using System.Web.Services.Protocols;

namespace wstest
{
   [WebService(Namespace = "http://tempuri.org/")]
   [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
   [System.ComponentModel.ToolboxItem(false)]
   public class Service1 : System.Web.Services.WebService
   {
      public ServiceAuthHeader CustomSoapHeader;

      [WebMethod]
      [SoapHeader("CustomSoapHeader")]
      public string HelloWorld()
      {
         ServiceAuthHeaderValidation.Validate(CustomSoapHeader);
         return "Hello World";
      }
   }


   public class ServiceAuthHeader : SoapHeader
   {
      public string Username;
      public string Password;
   }
 
 
   public class ServiceAuthHeaderValidation
   {
      public static bool Validate(ServiceAuthHeader soapHeader)
      {
         if (soapHeader == null)
         {
            throw new NullReferenceException("No soap header was specified.");
         }
         else if (soapHeader.Username == null || soapHeader.Password == null)
         {
            throw new NullReferenceException("Username and password are required.");
         }
         else if (soapHeader.Username != "myuser" || soapHeader.Password != "mypass")
         {
            throw new NullReferenceException("Provide correct values for username and password.");
         }
         return true;
      }
   }
   
}

-------------------------- websvc.cs ----------------------

Launch the service, create a console app, right click “Service References” and select “Add Service Reference”, paste url to WSDL for web service.

-------------------------- client.cs ----------------------

using System;
using System.Collections.Generic;

namespace ConsoleApplication2
{
   class Program
   {
      static void Main(string[] args)
      {
         string s;
         ServiceReference1.Service1SoapClient svc = new ServiceReference1.Service1SoapClient();
         ServiceReference1.ServiceAuthHeader hdr = new ServiceReference1.ServiceAuthHeader();
         hdr.Username = "myuser";
         hdr.Password = "mypass";
         s = svc.HelloWorld(hdr);
         Console.WriteLine(s);
      }
   }
}

-------------------------- client.cs ----------------------

USE master
GO

ALTER PROCEDURE [dbo].[sp_searchit] ( @val varchar(255), @escape char(1) = NULL, @table sysname = NULL )
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @cols TABLE ( id int identity(1,1), colname sysname, tabname sysname )
   DECLARE @results TABLE ( id int identity(1,1), colval varchar(max), colname sysname, tabname sysname )

   DECLARE @id int
         , @colname sysname
         , @tabname sysname
         , @sql nvarchar(4000)

     INSERT INTO @cols ( colname, tabname )
          SELECT A.name "colname"
               , B.name "tabname"
            FROM syscolumns A (NOLOCK)
      INNER JOIN sysobjects B (NOLOCK)
              ON A.id = B.id
           WHERE A.type IN ( SELECT B.type FROM systypes B (NOLOCK) WHERE B.name IN ( 'char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext', 'sysname' ) )
             AND B.type = 'U'
             AND ( @table IS NULL OR B.name = @table )

          SELECT @id = max(id) 
            FROM @cols

   WHILE @id > 0
    BEGIN
      SELECT @colname = A.colname
           , @tabname = A.tabname
        FROM @cols A
       WHERE A.id = @id

      SET @sql = 'SELECT TOP 1 [' + @colname + '] "result", ''' + @colname + ''' "column", ''' + @tabname + ''' "table" FROM [' + @tabname + '] WHERE [' + @colname + '] LIKE ''%' + @val + '%'''
      IF @escape IS NOT NULL
       BEGIN
         SET @sql = @sql + ' ESCAPE ''' + @escape + ''''
       END

      INSERT INTO @results ( colval, colname, tabname )
      EXEC sp_executesql @sql

      SET @id = @id - 1
    END

          SELECT * 
            FROM @results
END --sp_searchit

GO

Nope, you don’t need no stinkin’ law of attraction, yoga, contemplative breath prayers, 12 steps to paganism, or 3 points and a poem to follow the road to enlightened performance.  Just follow this simple formula:

Step 1: Rewrite them as stored procedures…

      public static string cmdToString(SqlCommand cmd)

      {

         var c = ” “;

         var s = cmd.CommandText;

         for (int i = 0; i < cmd.Parameters.Count; i++)
         {
            s += c + cmd.Parameters[i].ParameterName;
            s += " = " + cmd.Parameters[i].Value;
            c = ", ";
         }
         return (s);
      }

Ran into a real winner today.  For a completely unexpected reason, several views began timing out and not returning results.

The only thing that worked was recreating the views.

I suspect a possible cause is that the views reference synonyms that in turn reference objects across a linked server.  The linked server options had been modified right before the views stopped working.