jump to navigation

Migrar usuarios SQL SERVER September 6, 2010

Posted by juanpablo1manrique in DAtabase, Seguridad, SQL SERVER, SQL SERVER 2008, Uncategorized.
Tags:
add a comment

Para migrar usuarios entre servidores de SQL SERVER se Puede seguir este maravilloso Post que encontre:

http://support.microsoft.com/kb/918992

Hacer backups que incluyan los usuarios de base de datos y recuperarlo en la instancia destino, esto solo aplica de 2005 a 2008.

Crear este SP en el Master:

USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint – (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT ‘No login(s) found.’
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@type IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group

      SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
        — obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        — obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
      SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
      SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
      SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Luego ejecutar desde el master  el procedimiento EXEC sp_help_revlogin, el resultado de este procedimiento ejecutarlo en la instancia destino. Y Listo !!

 

Advertisements

Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Failed to open the explicitly specified database. March 26, 2010

Posted by juanpablo1manrique in DAtabase, event viewer, IT, SQL SERVER, SQL SERVER 2008, Windows 2008.
Tags:
add a comment

A veces por alguna razon todo esta bien y pumm uno no se conecta y sale un error come este en el eventviewer

Login failed for user ‘NT AUTHORITY\SYSTEM’. Reason: Failed to open the explicitly specified database. [CLIENT: x.x.x.x]

Entonces toca iniciar una sesión por consola, y cambiar el dafault_database

 – Sql autentication

 sqlcmd -S ISQLSHP -d TableroControlDW -U sa -P *******

 – windows autentication

sqlcmd -S CSQLSHP\ISQLSHP -d TableroControlDW

Luego ejecutar

1>ALTER LOGIN sa WITH DEFAULT_DATABASE = availableDB

2>GO

ó

1>ALTER LOGIN [domain\user] WITH DEFAULT_DATABASE = availableDB

 2>GO

Luego Enter Cuando se ejecuta el comando se le cambia la clave a sa Entonces en bueno ejecutar la sentencia

ALTER LOGIN sa WITH PASSWORD = ”

Cannot drop database “CompanyProducts” because it is currently in use March 12, 2010

Posted by juanpablo1manrique in DAtabase, SQL SERVER 2008.
Tags:
1 comment so far

Por lo general cuando se intenta borrar una base de datos en SQL 2008 no se puede porque existen procesos iniciados, en SQL 2008 no es tan facil darle Kill a los procesos que se encuentren iniciados que impiden borrar una base de datos, entonces es necesario crearse su propio SP que mate los procesos por uno, asi se ejecute el DROP DATABASE desde la consola aparece el mismo error. Por lo general uno intentaria buscar el Activity Monitor y matar estos procesos, pero desafortuandamente esto no es posible, desde el activity monitor actual, porque por alguna extraña razon no aparecen todos los procesos, y el campo de la Base de datos en el listado esta vacio.

Cannot drop database because it is currently in use. error 3702

CREATE PROCEDURE dbo.clearDBUsers 
    @dbName SYSNAME
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @spid INT,
        @cnt INT,
        @sql VARCHAR(255)
 
    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE dbid = DB_ID(@dbname)
        AND spid != @@SPID
 
    PRINT ‘Starting to KILL ‘+RTRIM(@cnt)+’ processes.’
    
    WHILE @spid IS NOT NULL
    BEGIN
        PRINT ‘About to KILL ‘+RTRIM(@spid)
 
        SET @sql = ‘KILL ‘+RTRIM(@spid)
        EXEC(@sql)
 
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE dbid = DB_ID(@dbname)
            AND spid != @@SPID
 
        PRINT RTRIM(@cnt)+’ processes remain.’
    END
END
GO

Tomado de http://sqlserver2000.databases.aspfaq.com/how-do-i-drop-a-sql-server-database.html

 

Best Practice : SharePoint Database Access March 11, 2010

Posted by juanpablo1manrique in Best Practices, DAtabase, Developer, SharePoint.
1 comment so far

Specifies any addition, modification, or deletion of the data within any SharePoint database by using database access commands. This includes bulk loading of data into a database, exporting data, or directly querying or modifying data.

Real World Example
A list aggregation Web Part that pulls information from a variety of sites in a server farm is designed to directly query the SharePoint database for information and present it to a user.

Technical Details
Directly querying or modifying the database can place extra load on a server, or can expose information to users in a way that violates security policies or personal information management policies. If server-side code must query data, then the process for acquiring that data should be through the built-in SharePoint object model, and not by using any type of query to the database. Client-side code that must modify or query data in SharePoint Products and Technologies can do this by using calls to the built-in SharePoint Web services that in turn call the object model.

Support Details
Important: 
This type of customization is not supported.
 

Direct modification of the SharePoint database or its data is not recommended because it puts the environment in an unsupported state.

If a server component requires information from the database, it must get that data by using the appropriate items in the SharePoint object model, and not by trying to get the items from the data structures in the database through some query mechanism.

Versión en espeañol:: Buenas Prácticas: Acceso a base de datos