Transact-SQL Sécurité

De Banane Atomic
Aller à la navigationAller à la recherche

Login

Tsql.svg 
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 '%##%'

Grant

Tsql.svg 
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

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

Role

Tsql.svg 
-- 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>';
Récupérée de « http://wiki.bananeatomic.fr/index.php?title=Transact-SQL_Sécurité&oldid=20201 »