Les changements de sysusers et sysxlogins dans SQL 2008
Je suis actuellement à la mise à jour d'un MS SQL server 2000 vers SQL 2008. L'un des problèmes mis en évidence par le conseiller de Mise à niveau est que les sans-papiers table sysxlogins a été supprimé.
J'ai actuellement une procédure qui est exécuté par un utilisateur 'toto' pour déterminer si l'utilisateur 'bar' existe dans la base de données bla. Si l'utilisateur existe le mot de passe utilisateur est comparé au mot de passe qui a été transmis à la procédure afin de déterminer si la barre est autorisé à se connecter à une application, il ressemble à ceci:
@UserName Varchar(50),
@Password Varchar(50)
As
Set NoCount On
------------------------------------------------------------------------------------
-- Check username
------------------------------------------------------------------------------------
If Exists
(
select top 1 name
from blah.dbo.sysusers With (NoLock)
where name = @UserName
)
Begin
------------------------------------------------------------------------------------
-- Check Password
------------------------------------------------------------------------------------
If Not Exists
(
Select *
From master.dbo.sysxlogins With (NoLock)
Where srvid IS NULL
And name = @Username
And ( ((@Password is null) or (@Password = '') and password is null)
Or (pwdcompare(@Password, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1))
)
Begin
Return 2
End
Else
Begin
------------------------------------------------------------------------------------
-- Check Role
------------------------------------------------------------------------------------
Select usg.name
From blah.dbo.sysusers usu
left outer join (blah.dbo.sysmembers mem inner join blah.dbo.sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
left outer join syslogins lo on usu.sid = lo.sid
where usu.name = @Username
and usg.name not like 'db_%'
Return 0 -- Username and password correct
End
End
Else
Begin
Return 1 -- Username incorrect
End
Tout cela fonctionne bien sous SQL 2000, mais je dois maintenant payer le prix de sans-papiers à l'aide de tables système et le faire fonctionner sous 2008.
Il y a deux problèmes, le premier problème est que toto ne peut plus voir tous les utilisateurs de base de données lors de l'exécution:
select * from blah.dbo.sysusers
ou recommandées par Microsoft alternative:
select * from blah.sys.database_principals
Je comprends que cela est dû au fait que les membres de la fonction publique n'ont plus accès à l'objet de méta-données, à moins qu'ils sont un membre du rôle sysadmin ou ont la Définition de l'Affichage de l'autorisation sur l'objet.
Il n'est pas possible de foo pour être membre du rôle sysadmin, pour autant que je comprends que j'ai besoin de subvention de foo la Définition de la Vue de l'autorisation, mais sur quel objet? Je ne pense pas que je le fais sur le système d'affichage, de sorte que dois-je faire à chaque utilisateur?
D'autre part, et de la même façon, j'ai besoin de changer ma référence à sysxlogins à sys.sql_logins. Nouveau foo ne peut se voir lui-même et de sa lors de l'exécution de
select * from sys.sql_logins
Comment puis-je obtenir foo afin d'afficher toutes les connexions de serveur dans cette liste?
Il y aura sans doute des problèmes similaires lors de l'accès à sysmembers et syslogins plus tard dans le code mais j'espère que la compréhension des deux exemples ci-dessus va m'aider à trier le reste.
Merci d'avance,
Vous devez vous connecter pour publier un commentaire.
Vous pouvez accorder la sélection directement sur sys.database_principals, aussi longtemps que la connexion a un utilisateur dans la base de données master. Par exemple:
Puis, dans SQL Server 2008, les mots de passe sont cryptés, même pour l'administrateur. Vous pouvez, toutefois, de vérifier un mot de passe en essayant de le changer. La procédure de changement donnera une erreur si l'ancien mot de passe est incorrect.
Pour que cela fonctionne, vous devez désactiver
Enforce password policy
dans le dialogue Propriétés de la Connexion. Sinon, la stratégie serait de vous empêcher de changer votre mot de passe trop souvent.WITH EXECUTE AS OWNER
qui vérifie si un utilisateur existe. Parce que la procédure s'exécute en tant que propriétaire de base de données, il devrait être en mesure d'afficher tous les utilisateurs.Je pense que
GRANT SELECT ON...
est plus gênant car, il faut ajouter l'utilisateur à la base de données master. Le ci-dessous a été la solution pour moi:Si vous avez une application qui fonctionne sur les différentes versions de SQL, vous devez vérifier si la version du serveur est supérieur à 8 (SUBVENTION d'AFFICHER une DÉFINITION des œuvres à partir de SQL 2005 mais il semble que ne pas être nécessaire).