Tuesday, September 23, 2008

Scripting role members in every database using PowerShell.

Hi,
Starting this post I will be writing in English, in order to reach the users from the PowerShell Community.

Previously, I shared some scripts written in PowerShell to generate automatically some configurations (like users, jobs, linked servers, etc) in SQL Server 2005 using SMO with PowerShell.

In this post, I wrote a script to generate the users in every database, that script had an error, the script generated needed a GO command before the instruction to add the user to their respective role.

So, I created this script to generate a script with the sys.sp_addrolemember instruction for every role,it is helpful if you want to restore any database in other server (you would need first to recreate the users with the other script) or in case a Disaster Recovery scenario.

So, this is the script:

param (
[string] $serverName
)
$Scripts = "c:\scripts\"

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" $serverName

foreach($db in $srv.databases) {
if ($db.Name -ne "master" -and $db.Name -ne "model" -and $db.Name -ne "msdb" -and $db.Name -ne "tempdb") {
$f = [System.IO.Path]::Combine($Scripts, $srv.Name +"_roles_" + $db.Name + ".sql")
out-file -filePath $f -inputobject "USE $db"
out-file -filePath $f -inputobject "GO" -Append
foreach ($role in $db.Roles) {
if ($role.Name -ne "public") {
foreach ($member in $role.EnumMembers()) {
if ($member -ne "dbo") {
$var = "sys.sp_addrolemember @rolename = N'" + $role.Name+"', @membername = N'"+$member+"'"
out-file -filePath $f -inputobject $var -append
out-file -filePath $f -inputobject "GO" -append
}
}
}
}
}
}


To run this script, you just need to specify the sql server and it will generate in c:\scripts a file for every database containing the script to add the members for every user, just like this:

USE [MyDB]
GO
sys.sp_addrolemember @rolename = N'HR', @membername = N'user1'
GO
sys.sp_addrolemember @rolename = N'sales', @membername = N'user2'
GO

Wednesday, September 10, 2008

Mas de los esquemas en SQL 2005.

En un post anterior comentaba acerca de lo recomendable que era que los owners de los esquemas no debían ser los mismo usuarios, para evitar problemas en futuras migraciones.

Pues bien, según una entrada del blog de Microsoft ISV Program Management Team, lo mejor que puedes hacer después de haber migrado a SQL 2005 es cambiar el default esquema de los usuarios a dbo, esto te dará un aumento de hasta un 15% en el throughput del servidor, esto, si en las llamadas que se hacen a los objetos no se incluye "dbo", que es un best practice, por cierto.

Se puede cambiar el default esquema de los usuarios fácilmente con el comando ALTER USER WITH DEFAULT_SCHEMA=dbo

Se puede utilizar este script para obtener como respuesta el script que se tiene que correr en cada base de datos:

select 'alter user '+ name + ' with DEFAULT_SCHEMA=dbo;'
from sys.database_principals
where default_schema_name is not null and name<>'dbo' and name <>'guest' and default_schema_name<>'dbo'

Esto se corre en cada base de datos, en la que se desea hacer y se obtiene de resultado el script con el que se cambian todos los usuarios rápidamente.

Una vez que se cambió el default esquema de los usuarios, lo recomendable es eliminar los esquemas de los usuarios, con este script se pueden obtener el script para eliminar los esquemas por cada base de datos:

select 'DROP SCHEMA '+ name + ';'
from sys.schemas
where schema_id between 5 and 16383

Wednesday, September 3, 2008

Detectando logins huérfanos.

Un script que puede ser útil para quienes tienen servidores SQL 2005 y que necesitan saber rápidamente si tienen en sus servidores logins que ya nadie los ocupa, es decir, no están como usuarios en ninguna base de datos, no están como local user en ningún linked server, no pertenecen a ningún rol de servidor y están habilitados.

Aquí está el código:

DECLARE @dbname2 varchar(40);
DECLARE dbname CURSOR FOR select name from master.sys.databases';

OPEN dbname
FETCH NEXT FROM dbname
INTO @dbname2

create table #nombres(
nombre varchar (128))

WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET NOCOUNT ON;
use [' + @dbname2 + ']
INSERT #nombres
select b.name
from sys.database_principals a inner join sys.server_principals b on a.sid=b.sid
where a.type_desc <> ''DATABASE_ROLE'' and a.name <> ''dbo''
and a.name <> ''guest'' and a.name <> ''INFORMATION_SCHEMA'' and a.name <> ''sys'' ')
FETCH NEXT FROM dbname INTO @dbname2
END

CLOSE dbname
DEALLOCATE dbname

DECLARE users CURSOR FOR select distinct(nombre) from #nombres order by nombre;
declare @usuario varchar(128);

OPEN users
FETCH NEXT FROM users
INTO @usuario

create table #sids(
sid varbinary(256),
nombre varchar(128) )

WHILE @@FETCH_STATUS = 0
BEGIN
exec('SET NOCOUNT ON;
INSERT #sids
select suser_sid('''+ @usuario +'''),'''+@usuario+'''')
FETCH NEXT FROM users INTO @usuario
END

CLOSE users
DEALLOCATE users

select a.name as login
from sys.server_principals a left join #sids b on a.sid=b.sid
where b.sid is null and a.type_desc <>'CERTIFICATE_MAPPED_LOGIN' and a.type_desc <>'SERVER_ROLE' and a.name <> 'sa'
and a.name <>'BUILTIN\Administrators' and a.name <>'NT AUTHORITY\SYSTEM' and a.principal_id<>259
and a.principal_id not in (select distinct(local_principal_id) from sys.linked_logins) and a.is_disabled=0
and a.principal_id not in (select member_principal_id from sys.server_role_members)
order by a.name

drop table #nombres
drop table #sids