Migrar usuarios SQL SERVER September 6, 2010
Posted by juanpablo1manrique in DAtabase, Seguridad, SQL SERVER, SQL SERVER 2008, Uncategorized.Tags: SQLSERVER2008
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 !!
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: SQLSERVER2008
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: SQLSERVER2008
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