Transact-SQL Sécurité

Login

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>'; GO ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>'; DROP LOGIN <login_name> -- Get the list of all SQL Login Accounts only SELECT name FROM sys.server_principals WHERE TYPE = 'S' and name not like '%##%'

• Lister les logins

Grant

GRANT INSERT, UPDATE, SELECT ON <Database> TO <User> -- permission de créer des bdd use [master] GO GRANT CREATE ANY DATABASE TO <LoginName> GO

User

-- lier un login à un user de bdd USE <DatabaseName> GO CREATE USER <UserName> FOR LOGIN <LoginName> GO

Role

-- lister les membres des roles d'une bdd SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' ORDER BY DP1.name; -- ajouter un Role à un User ALTER ROLE [db_owner] ADD MEMBER <UserName> GO -- avec une PS EXEC sp_addrolemember 'db_owner', '<UserName>';