Fév 02

Afin de gérer un problème de droits, j’ai eu besoin d’avoir la liste des utilisateurs de la base de données avec leurs droits effectifs sur les différents objets.

Cette question a déjà été posée sur Stack Overflow et une des réponses me donne exactement ce dont j’ai besoin !

    --1) List all access provisioned to a SQL user or Windows user/group directly
    SELECT
        [UserType] = CASE princ.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = princ.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = NULL,
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Database user
        sys.database_principals            AS princ
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]
Jan 12

Une petite astuce que j’ai croisé sur comment calculer l’âge facilement sur Sql Server:

select (0 + CONVERT(char(8),GETDATE(),112) - CONVERT(char(8),'19921015',112)) / 10000

Le fait de diviser par 10000 calcule la différence des années (suite à la transformation du format YYYYMMJJ en nombre) tout en prenant en compte le nombre de jours depuis le début de l’année. On donne ainsi juste une représentation décimale croissante au nombre de jours depuis le début de l’année, cette représentation peut être arbitraire, tant que c’est croissant.

Donc les décimales résultant de la soustraction ne signifient pas grand-chose en elles-mêmes (ça n’indique pas l’intervalle en nombre de jour par exemple).

Juil 30

SQL-Server-2012Certaines opérations sur les bases de données Sql Server nécessitent qu’il n’y ait plus aucun utilisateur connecté à la base de données, par exemple la restauration d’un backup.

Le problème, c’est que Sql Server n’est pas très généreux en ce qui concerne les outils d’administration et qu’il n’est pas toujours simple de savoir si des utilisateurs sont connectés ou de les déconnecter.

La solution passe donc par quelques commandes SQL.

Pour désactiver la base de données en déconnectant tous les utilisateurs :

ALTER DATABASE db_AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE

Et pour la réactiver une fois l’opération terminée :

ALTER DATABASE db_AdventureWorks SET ONLINE
Juil 24

sqlserver_connectUn problème qui finit toujours par arriver aux utilisateurs de Sql Server 2008 : l’interface de connexion finit par être polluée par de nombreux serveurs ou utilisateurs qui ne sont pas ou plus utilisés !

En effet, Sql Server a tendance à enregistrer un peu tout ce que l’on saisit que ce soient les serveurs ou les utilisateurs, même les erreurs ou des logins qui ne fonctionnent pas.

Ainsi, il arrive assez souvent que l’on ait envie de supprimer des éléments de ces listes qui ne font que s’allonger. Apparemment c’est une question que beaucoup se posent et malheureusement la réponse la plus courante ne me satisfait pas, car elle implique de supprimer toutes les valeurs, au lieu de juste supprimer celles qui ne nous intéressent pas.

La solution pour supprimer uniquement les valeurs que l’on souhaite est de passer par le logiciel Sql Server 2008 MRU Updater qui se charge de faire les modifications.
Je l’ai testé et il marche nickel !

A noter que cette fonctionnalité a été intégrée à Sql Server 2012.

Fév 25

SQL-Server-2012Une chose assez ennuyeuse dans SQL Server Management Studio (SSMS), c’est que la fonction de génération de scripts SQL n’inclut pas par défaut les index. Il y a probablement (enfin j’espère) une bonne raison derrière ce choix, mais il s’avère que dans la pratique, j’ai systématiquement besoin que ces index soient présents et je me suis fait avoir plusieurs fois, à me retrouver avec des soucis de performances suite à une livraison.

Ainsi, avec les options par défaut, les index (autres que les clés primaires, étrangères et index uniques) ne sont pas générés lorsqu’on demande la génération du script SQL pour la création d’une table.

Pour changer cela, il suffit d’aller dans les options de SSMS, qui permettent de choisir toutes les valeurs par défaut pour la génération de script :
Donc, dans SSMS, aller dans Tools –> Options –> SQL Server Object Explorer –> Scripting et positionner Script indexes à True.

options-scripting-sql-server

Source : Forum MSDN